Hi all !
I have a table users, a table books and a table book_user between for many_to_many relationship :
users :
- id : int
- name : varchar
boooks :
- id : int
- name : varchar
- color : varchar
book_user :
- book_id : int
- user_id : int
I declared the relation in both Models
// in User.php :
public function books()
{
return $this->belongsToMany(Book::class);
}
// in Book.php
public function users()
{
return $this->belongsToMany(User::class);
}
I want to get all the distinct color of the books of a user (to make a list for a select in a filter area). If I make this call with eloquent :
return $myuser->books()->select('color')->distinct()->get();
I get duplicate colors because eloquent add the pivot table attribute in the select. Here is what I get in SQL :
SELECT DISTINCT
`color`,
`book_user`.`user_id` AS `pivot_user_id`,
`book_user`.`book_id` AS `pivot_book_id`
FROM
`books`
INNER JOIN `book_user` ON `books`.`id` = `book_user`.`book_id`
WHERE
`book_user`.`user_id` = ?
Do you know what I could do to get only the color in the select to perform a real distinct ? The SQL result should be
SELECT DISTINCT
`color`
FROM
`books`
INNER JOIN `book_user` ON `books`.`id` = `book_user`.`book_id`
WHERE
`book_user`.`user_id` = ?
To get only the color in the select to perform a real distinct, you can use the groupBy method instead of the distinct method. Here's an example:
return $myuser->books()->select('color')
->groupBy('color')
->get();
This will generate the following SQL query:
SELECT `color`
FROM `books`
INNER JOIN `book_user` ON `books`.`id` = `book_user`.`book_id`
WHERE `book_user`.`user_id` = ?
GROUP BY `color`
This will give you a list of distinct colors for the books of the user.
@LaryAI This is not a bad idea but I do not get the SQL query I expected, here is what I get :
SELECT
`color`,
`book_user`.`user_id` AS `pivot_user_id`,
`book_user`.`book_id` AS `pivot_book_id`
FROM
`books`
INNER JOIN `book_user` ON `books`.`id` = `book_user`.`book_id`
WHERE
`book_user`.`user_id` = 2
GROUP BY
`color`
And in my case I got this SQL error because I have more column in the SELECT than in the GROUP BY :
SQLSTATE[42000]: Syntax error or access violation: 1055 Expression 2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'book_user.user_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Try hiding the pivot
return $myuser->makeHidden('pivot')->books()->select('color')->distinct()->get();
Thank you @willvincent for your message ! Unfortunately I keep getting the same SQL query with pivot attributes in SELECT, makeHidden('pivot') seems to have no effect :(
Please or to participate in this conversation.