Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

Whats the best way to find a file in database in manyToMany Relation?

hi, i have the three models tags (which has a manytomany relation to the model tagsFiles), files (which has a manytomany relation to tagsFiles) and tagsFiles (which has relations to tags through belongsTo and files through belongsTo.

tags looks like: id, name tagsFiles: tagId, flieId files: id, name date, version

Now i want to find all files with a specific tag and a version (for delete)

with:

$tag = Tags::find(
            [
                "name = :tag:",
                "bind" => ["tag" => $data['tag']]
            ]);

    foreach($tag as $test){
             echo json_encode($test->tagsFiles);

i get all files with specific tag, but how can i add that i get only the files with a specific version and tag. I have to search in the tags and the files table...

something like:

$tag = Tags::find(
            [
                "name = :tag: AND version = :version:",
                "bind" => ["tag" => $data['tag'],
                    "version" => $data['version']
                ]
            ]);

doesn´t work

Thanks for help

edited May '18

Just pass the parameters when you call the relation. Works same as passing parameters and coditions to find() or findFirst().

foreach ($tag as $test) {
    $test->tagsFiles([
        'conditions' => ...
        'bind' => ....
    ]);
}


2.0k
edited May '18

with conditions i get no result. Without conditions i get all found files...

EDIT

i solved it. I have to use

foreach ($tag as $test) {
    $test->gettagsFiles([
        'conditions' => ...
        'bind' => ....
    ]);
}


2.0k
edited May '18

sorry i have an additional question.

now i found the correct entry in the database. how can i delete the entry?

foreach ($tag as $test) {
                echo json_encode($test->tagsFiles);
                $entry = $test->gettagsFiles([
                    "version = :version:",
                     'bind' => [
                         'version' => $version
                     ]
                ]);

                $entry->delete();
            }

doesn´t work :(



2.0k
edited May '18

i don´t get the delete function to run. i tried this:

$tag = Tags::findFirst(
            [
                "name = :tag:",
                "bind" => ["tag" => $data['tag']]
            ]);

// first try

foreach ($tag->tagsFiles as $tagFile) {

            if ($tagFile->version == 3)
                 $tagFile->delete();
        }

        //second try:

         $tag->tagsFiles()->delete(
                    function ($file) {
                        $version = "3";  // only placeholder for testing

                        if ($file->version != $version) {
                            return false;
                        }
                        return true;
                    }
                );

but no one of the two tries works. Have someone an idea? I want to find all files from a specific tag and then delete some of them...

here are my model files:

Tags:

public function initialize()
    {

        $this->hasManytoMany(
            'id',
            'App\Models\TagsFiles',
            'tag_id', 'file_id',
            'App\Models\Files',
            'id',
            array(
                'alias' => 'tagsFiles',
                'foreignKey' => array(
                    'action' => Relation::ACTION_CASCADE
               )
            )
        );
    }

TagsFiles:

public function initialize()
    {
        $this->belongsTo(
            'file_id',
            'App\Models\Files',
            'id',
            array(
                'alias'=> 'file',
                  'foreignKey' => [
                      'message'   => 'file can´t deleted at the moment'
                  ]
            )
        );

        $this->belongsTo(
            'tag_id',
            'App\Models\Tags',
            'id',
            array(
                'alias'=>'tag',
             //    'foreignKey' => [
             //        'action' => Relation::ACTION_CASCADE,
             //        'message'   => 'tag id does not exist or is currenty invalid'
             //    ]
            )
        );
    }

Files:

 public function initialize()
    {
        $this->hasManytoMany(
            'id',
            'App\Models\TagsFiles',
            'file_id', 'tag_id',
            'App\Models\Tags',
            'id',
            array(
                'alias'=>'tagsFiles',
                  'foreignKey' => array(
                     'action' => Relation::ACTION_CASCADE
                  )
            )
        );
    }
edited May '18

All the answers are bad, you should do it like this:

$modelsManager = $di->get('modelsManager'); // get modelsManager from di
$tags = $this->modelsManager->createBuilder()
    ->columns('TagsFiles.*')
    ->from(['TagsFiles'=>'App\Models\TagsFiles')
    ->leftJoin('App\Models\Files', 'TagsFiles.file_id = Files.id', 'Files')
    ->leftJoin('App\Models\Tags', 'TagsFiles.tag_id = Tags.id', 'Tags')
    ->where('Tags.name = :name: AND Files.version = :version:', ['name' => $data['name'], 'version' => 3]);
    ->getQuery()
    ->execute();
$tags->delete();

And that's it, all tags got in one query and deleted using single method. Though i prefer more to delete them in foreach to control what to do if there is no success with delete one of models.

Not sure exactly which tagsfiles you want to delete, as i understand by name and version. But you can modify this however you want.

If delete doesn't work, this most likely means you have some kind of exception or error. Try to check your logs or try to call getMessages() on model/resultset.



2.0k

Thanks for your answer. The reason why the delete function did not work was a error at my database.

Next i´ll give your solution a try.

Thanks to all of you



2.0k

All the answers are bad, you should do it like this:

$modelsManager = $di->get('modelsManager'); // get modelsManager from di
$tags = $this->modelsManager->createBuilder()
   ->columns('TagsFiles.*')
   ->from(['TagsFiles'=>'App\Models\TagsFiles')
   ->leftJoin('App\Models\Files', 'TagsFiles.file_id = Files.id', 'Files')
   ->leftJoin('App\Models\Tags', 'TagsFiles.tag_id = Tags.id', 'Tags')
   ->where('Tags.name = :name: AND Files.version = :version:', ['name' => $data['name'], 'version' => 3]);
   ->getQuery()
   ->execute();
$tags->delete();

And that's it, all tags got in one query and deleted using single method. Though i prefer more to delete them in foreach to control what to do if there is no success with delete one of models.

Not sure exactly which tagsfiles you want to delete, as i understand by name and version. But you can modify this however you want.

If delete doesn't work, this most likely means you have some kind of exception or error. Try to check your logs or try to call getMessages() on model/resultset.

That work nice for me. But when i execut $tags->delete() he only deltes the entry in the TagsFiles table. How can i change it that he deletes the entry in the TagsFiles table and the entry in the Files table.

If i change the line columns('TagsFiles.') to cloumns('Files.') i can delete the entry in the Files table but not in the TagsFiels table. I think i need something like columns('TagsFiles.', 'Files.') Is there something

I also tried the foreignKey in the model Files (see post before) but it doesn´t work.

Well you can just have cascade delete in your database schema relation, so when files is deleted it will also delete TagsFiles.



2.0k

i don´t know what you exactly mean.

The ACTION::CASADE have no effect for me:

here are my db tables:

DROP TABLE IF EXISTS `tags`;
CREATE TABLE IF NOT EXISTS `tags` (
  `id` int(11)  NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) UNIQUE ,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `files`;
CREATE TABLE IF NOT EXISTS `files` (
  `id` VARCHAR(36)  NOT NULL,
  `name` VARCHAR(50) NOT NULL,
  `extension` VARCHAR(5) NOT NULL,
  `version` INT(11) NOT NULL,
  `date` DATE NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE If EXISTS  `tags_files`;
CREATE TABLE IF NOT EXISTS `tags_files` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `tag_id` INT(11) NOT NULL,
  `file_id` VARCHAR(36) NOT NULL,
  PRIMARY KEY (`id`) -- ,
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Models: Tags:

public function initialize()
    {
         $this->hasManytoMany(
            'id',
            'App\Models\TagsFiles',
            'tag_id', 'file_id',
            'App\Models\Files',
            'id',
            array(
                'alias' => 'tagsFiles',
                'foreignKey' => array(
            //        'action' => Relation::ACTION_CASCADE
                    "message" => "The part cannot be deleted because other robots are using it",
                )
            )
        );
    }

TagsFiles;

public function initialize()
    {
        $this->belongsTo(
            'file_id',
            'App\Models\Files',
            'id',
            array(
                'alias'=> 'file',
                'reusable' => true,
                  'foreignKey' => [
                     // 'action' => Relation::ACTION_CASCADE,
                      'message'   => 'file can´t deleted at the moment'
                   ]
            )
        );

        $this->belongsTo(
            'tag_id',
            'App\Models\Tags',
            'id',
            array(
                'alias'=>'tag',
                 'foreignKey' => [
                     'action' => Relation::ACTION_CASCADE,
                     'message'   => 'tag id does not exist or is currenty invalid'
                 ]
            )
        );
    }

Files:

public function initialize()
    {
        $this->hasManytoMany(
            'id',
            'App\Models\TagsFiles',
            'file_id', 'tag_id',
            'App\Models\Tags',
            'id',
            array(
                'alias'=>'tagsFiles',
                  'foreignKey' => array(
                     'action' => Relation::ACTION_CASCADE,
                  )
            )
        );
    }

and the command to delete:

$Files =$this->modelsManager->createBuilder()
                       ->columns('TagsFiles.*')
                        ->from(['TagsFiles'=>'App\Models\TagsFiles'])
                       ->leftJoin('App\Models\Files', 'TagsFiles.file_id = Files.id', 'Files')
                        ->leftJoin('App\Models\Tags', 'TagsFiles.tag_id = Tags.id', 'Tags')
                   ->where('Files.version = :version:', ['version' => '6'] )
                        ->getQuery()
                        ->execute();
                    $Files->delete();

if execute this he only delete the entry in TagsFiles and not in files too.

Do i missunderstand the Foreign key Action::CASCDE?

Thanks for your advance help



132.9k
Accepted
answer
edited May '18

I mean in DATABASE schema, not in phalcon. https://stackoverflow.com/questions/1571581/how-to-add-on-delete-cascade-in-alter-table-statement.

In phalcon not sure if adding foreignKey on manyToMany relation and Relation::ACTION_CASCADE, will delete records most likely not, you would need co add hasMany with Relation::ACTION_CASCADE like:

Files:

public function initialize()
    {
        $this->hasManytoMany(
            'id',
            'App\Models\TagsFiles',
            'file_id', 'tag_id',
            'App\Models\Tags',
            'id',
            array(
                'alias'=>'tags',
            )
        );
        $this->hasMany(
            'id',
             'App\Models\TagsFiles',
            'file_id'
             array(
                'alias'=>'tagsFiles',
                  'foreignKey' => array(
                     'action' => Relation::ACTION_CASCADE,
                  )
            )
    }

And then when deleting files it should delete tagsFiles. But it's better to put cascade delete in database schema imho, no need to framework do it(it will be slower)



2.0k

thankys for your help. I created the delete cascade at my database shema. Now i have the problem to insert data into the two tables:

$file_id = str_replace('-', '', $data['uuid']);

            $tags_files->setTagId($data['tag_id'])
                ->setFileId($file_id)

       $result = $files->setId($file_id)
                ->setName($file_name[0])
                ->setDate($data['current_Date']->format('Y-m-d'))
                ->setExtension($uploads->getExtension())
                ->setVersion($data['version']);

            $tags_files->files = $result;

            $tags_files->create();

that does´t work. the function create returns always false.



2.0k

ok i found the mistake by myself. Thanks evryone