CODEWITHSUNDEEP
phpmysqllaravel-4
egekhter
egekhter

Reputation: 2225

Laravel: For each where clause

I'm trying to return a query that has an arbitrary amount of where clauses based on the number of tags a user submits.

//unknown number of ids to query on
$tag_ids = array(1,5);

//multiple joins with closure
$items = DB::table('archives_items_metadata')->join('tags', 'archives_items_metadata.tag_id', '=', 'tags.id')->join('archives_items', 'archives_items_metadata.archive_item_id', '=', 'archives_items.id')->join('items', 'archives_items.item_id', '=', 'items.id')
                ->where(function ($query) use ($tag_ids) {

                    foreach ($tag_ids as $tag_id)
                    {
                        $query->where('archives_items_metadata.tag_id', $tag_id);
                    }

                })->get();

The result I get is an empty array even though when I try array(1) or array(5) by themselves, they both return the same item. What am I missing?

EDIT:: I'm looking to return items that have each of the tag ids specified. The reference of items to tags is stored on the archives_items_metadata table. How can I get the result I'm expecting, and what's the most efficient way to accomplish this?

Upvotes: 1

Views: 1112

Answers (2)

egekhter
egekhter

Reputation: 2225

$tag_count = count($tag_ids);
$items = DB::table('archives_items')->join('archives_items_metadata', 'archives_items_metadata.archive_item_id', '=', 'archives_items.id')->join('tags', 'archives_items_metadata.tag_id', '=', 'tags.id')
->whereIn('archives_items_metadata.tag_id', $tag_ids)->whereNull('archives_items_metadata.deleted_at')
->groupBy('archives_items_metadata.archive_item_id')->havingRaw('count(*)='.$tag_count->get();

Upvotes: 0

Victory
Victory

Reputation: 5890

You are looking to do a WHERE tag_id IN (1, 2, 3) style clause laravel has the whereIn($col, $vals) builder function.

->whereIn('archives_items_metadata.tag_id', $tag_ids)

search for "whereIn" in the official docs

Upvotes: 1

Related Questions