Retrieving data with and without using a function

Need Help
1

Hi coders, I have one maybe stupid question today but based on text in cookbook (Retrieving Data & Results Sets - 4.x) what is advantage of this query

$query = $articles->find();
$query->innerJoinWith(‘Tags’, function ($q) {
return $q->where([‘Tags.name’ => ‘CakePHP’]);
});

over

$query = $articles->find()
->innerJoinWith(‘Tags’)
->where([‘Tags.name’ => ‘CakePHP’]);

?

What does

someMethod(‘keyword’, function())

means and why do we use just this kind of notation?

Thank you!

2

TLDR: They give you the same result but one does a typicall WHERE whereas the other does a ON() inside the JOIN which can be helpfull depending on your query.

In situations like this I would recommend you look into the Sql Log Tab of the debug_kit
In there you will see what kind of SQL is being executed.

In your specific case I would guess Articles belongsToMany Tags and therefore

$this->Articles->find()
   ->innerJoinWith('Tags')
   ->where(['Tags.name' => 'CakePHP'])
   ->toArray();

will result in something like

SELECT
  Articles.id AS Articles__id,
  Articles.name AS Articles__name,
  Articles.created AS Articles__created,
  Articles.modified AS Articles__modified
FROM
  Articles Articles
  INNER JOIN articles_tags ArticlesTags ON Articles.id = ArticlesTags.article_id
  INNER JOIN tags Tags ON Tags.id = ArticlesTags.tag_id
WHERE
  Tags.name = 'CakePHP';

but

$this->Articles->find()
   ->innerJoinWith('Tags', function($q){
       return $q->where(['Tags.name' => 'CakePHP']);
   })
   ->toArray();

will result in

SELECT
  Articles.id AS Articles__id,
  Articles.name AS Articles__name,
  Articles.created AS Articles__created,
  Articles.modified AS Articles__modified
FROM
  Articles Articles
  INNER JOIN articles_tags ArticlesTags ON Articles.id = ArticlesTags.article_id
  INNER JOIN tags Tags ON (
    Tags.name = 'CakePHP' AND
    Tags.id = ArticlesTags.tag_id
  );

As you see the where() part is being handled differently because its part of the join in the 2nd example.

Both queries will give you the same result and will most likely be executed in the same time as well.

I won’t go into too much detail but its just that in certain situations its nice to have the choice to place that “where” check inside the join OR inside a typicall WHERE part. Depends on your associations, what you want to achieve and how you further want to adjust your query down the road.

1 Like
3

Thank you very much for detailed explanation.