php - Laravel join and distinct -
in laravel, have query is:
$productsmodel->join('tags', 'tags.item_id', '=', 'products.id') ->select('products.*', 'tags.item_id') ->distinct(); db tables looks this: | products | tags | ----------|---------| | id | id | | | item_id |
products has 1 many relationship tags, creates issues when joining tables. example, need query products based on multiple tags, need show 1 product.
okay. you're going have provide little more context.
from looks of things, have 1:n relationship, tablea has many tableb. therefore, if join, may have multiple tableb records joined tablea records, fine.
but then, if want select distinct tablea record, of many possible tableb records should returned, if there's more one?
an easy way achieve you're asking use group by
.
$tablea->selectraw('table_a.*, max(table_b.item_id) item_id') ->join('table_b', 'table_a.id', '=', 'table_b.item_id') ->groupby('table_a.id') ->get();
it's worth mentioning selecting table_b.item_id
isn't necessary, because value equal table_a.id
. saying.
edit - based on updated question, if want find products specific tags, can use where in
, not select field table_b
.
$tablea->select('table_a.*') ->join('table_b', 'table_a.id', '=', 'table_b.item_id') ->wherein('table_b.id', [1, 2, 3]) ->groupby('table_a.id') ->get();
Comments
Post a Comment