I have two tables:
Books
id
publisher_id
Publishers
id
name
Books is associated with Publishers in the BooksTable class like so:
public function initialize(array $config){
$this->belongsTo('Publishers');
}
I want to display a list of books along with their publishers, here is my code:
$query = $this->Books->find()
->contain('Publishers')
->select(['id', 'title', 'Publishers.name']);
$this->paginate($query);
This works fine, however I noticed that it produces the following queries:
SELECT * FROM books Books
LEFT JOIN publishers Publishers ON Publishers.id = (Books.publisher_id)
ORDER BY Books.id desc LIMIT 20 OFFSET 0
SELECT (COUNT(*)) AS `count` FROM books Books
LEFT JOIN publishers Publishers ON Publishers.id = (Books.publisher_id)
The Pagination component automatically strips out the unneeded code such as GROUP, ORDER, etc. from the second count query but it keeps the LEFT JOIN.
Is there a reason why this isn't removed and is there a way to tell it to ignore certain associations when querying the row count?
Say for example following is data for your table.
Books
id | publisher_id
-----------------
1 | 1
2 | 1
Publishers
id | name
----------
1 | publisher1
1 | publisher2
LEFT JOIN output
book_id | publisher_id | name
-----------------------
1 | 1 | publisher1
1 | 1 | publisher2
2 | 1 | publisher1
2 | 1 | publisher2
I know, For your case publisher_id is unique. but that's not case all time. Now you can see that LEFT JOIN has row count 4 while only 2 book there.