Creating multivalue search, struggling with SQL

Question

Based on user input, which could be a single or multiple values, and using the following table

+------+--------+
| seed |  item  |
+------+--------+
|    1 | apple  |
|    1 | grapes |
|    2 | apple  |
|    3 | grapes |
|    3 | banana |
+------+--------+

I want to return

1 when the user entered (apple, grape),
[1, 2] for (apple), and
nothing for (apple, banana).

My current PHP code

$keyword = Input::get('keyword');
        $searchTerms = explode(",", $keyword);
        $query = DB::table('items');
        foreach($searchTerms as $term)
        {   
            $query->where('item', 'LIKE', '%'.$term.'%');
        }
        $results  = $query->distinct()->get(array('seed'));

works for single values. Iterating in the loop, I'm just appending more search terms to the current $query. At the end, I need to find the intersection of all the queries. This is currently my main concern.

With no no indication, which database is being used, no table structure and no sample data, it is hard to understand what exactly, you want to achieve - and even harder to provide you with a useful response. — Abecee, Nov 30 '14 at 18:17
@Abecee Sorry! It's mysql I've drawn the relevant parts of my table here http://i.imgur.com/er3GD9b.png and an example would be (search: apple, grape returns: 1) (search: apple returns: 1 2) (search: grape, banana return: 3) — Rosheph Jackson, Nov 30 '14 at 18:23
Thanks for working on your question. But, please, give it another try. It's better to write out the sample data instead of linking to a picture. And your algorithm for picking the key based on the submitted values is still kind of blurred (apart from / possibly due to the fact, the last sentence has been cut off). — Abecee, Nov 30 '14 at 18:43
@Abecee I've tried but I'm not sure how to make the question any more clear then with my example. I've added my current search function — Rosheph Jackson, Nov 30 '14 at 18:52
Data is still in an external picture, not in the text of your inquiry. In your first paragraph, you want to return value2 for matching "all three keys". ?? And in the same first paragraph: Which "both" do you want to return for the removed key 3? — Abecee, Nov 30 '14 at 19:09
Please check my edits, and advance from here if appropriate. — Abecee, Nov 30 '14 at 19:40

score 0 · Accepted Answer · answered Dec 01 '14 at 00:50

0

With the input available try

SELECT seed
FROM Items
WHERE item IN ('apple', 'grapes')
GROUP BY seed
HAVING COUNT(item) >= 2
;

SELECT seed
FROM Items
WHERE item IN ('apple')
GROUP BY seed
HAVING COUNT(item) >= 1
;

SELECT seed
FROM Items
WHERE item IN ('apple', 'banana')
GROUP BY seed
HAVING COUNT(item) >= 2
;

The total to compare against in the HAVING clause is the count of items, you are checking in the respective batch.

SQL Fiddle

Please comment, if further detail or adjustment is required.

answered Dec 01 '14 at 00:50

Abecee

2,365
2
12
20

Fantastic! Thank you very much! Just so if anyone ever finds this the laravel equivalent is $results = DB::table('items')->select('seed')->whereIn('item',$searchTerms)->groupBy('seed')->having(DB::raw('count(item)'),'>=', $num)->get(); – Rosheph Jackson Dec 01 '14 at 02:22

Creating multivalue search, struggling with SQL

1 Answers1