PHP query help - join/where/outside?? :S

[likwid]

Hi

I'd really appreciate if someone could help me with this query.

I have three tables

---items---
| id
------------
| name
| desc
| catId
------------

---cats----
| id
|----------
| name
| desc
------------

--itemToks--
| itemId
| tok
|-------------
--------------

cats is just the category each item falls under.

itemToks is defined search strings which relate to each item. The point of which is to allow the user to search for items using various characteristics.

----------

I would like to be able to search for complete match(es) of itemToks.tok using more than one search term (where itemToks.tok IN ('tok1', 'tok2') ?) and return each category and the number of instances of matches.

So far I have this which works but isn't a search and returns all categories :

select cats.id as id, cats.name as name, count(items.cat) as count from cats left outer join items on items.cat = cats.id group by items.cat

Hopefully i've explained this ok, please let me know if otherwise. Help would be very much appreciated as I've wasted too much time on this already.

 

[likwid]

Just wrote up what is perhaps a clearer explanation. Yes, i'm still trying to figure this out 8 hours later...

It goes like this. I have three tables, one is of items, another of categories which each item falls into one of, the other tokens which each item has multiples of.

The point of the tokens are to allow the user to search for an item by multiple characteristic. For example, something may be 'red' and 'round', i need my query not to use an IN which is 'red' or 'round'.

The query would return the categories rather than the items and the number of found items within each category.

Here are the tables:

items
+------+-------+--------------------+
|id | name | desc | itemCat|
+------+-------+--------------------+
| 1 | item 1 | item desc 1 | 4 |
| 2 | item 2 | item desc 2 | 3 |
| 3 | item 3 | item desc 3 | 1 |
| 4 | item 4 | item desc 4 | 3 |
+------+-------------------|--------+


cats
+----+-------------------+
| id | name | des |
+----+-------------------+
| 1 | cat 1 | Cat desc 1 |
| 2 | cat 2 | Cat desc 2 |
| 3 | cat 3 | Cat desc 3 |
| 4 | cat 4 | Cat desc 4 |
| 5 | cat 5 | Cat desc 5 |
| 6 | cat 6 | Cat desc 6 |
| 7 | cat 7 | Cat desc 7 |
+----+------+------------+


itemToks
+-------+---------+
| itemId | tok |
+-------+---------+
| 1 | green |
| 1 | round |
| 1 | large |
| 2 | small |
| 2 | yellow |
| 3 | small |
| 3 | blue |
| 4 | small |
| 4 | red |
+-------+---------+

What I would hope returned is something like this when searching for small:

+----+-----+-------+
| id | cat | count |
+----+-----+-------+
| 1 | Cat2 | 1 |
| 2 | Cat3 | 0 |
| 3 | Cat4 | 1 |
| 4 | Cat5 | 0 |
+----+-----+-------+

and something like this when searching for small and red:


+----+-----+-------+
| id | cat | count |
+----+-----+-------+
| 1 | Cat2 | 0 |
| 2 | Cat3 | 0 |
| 3 | Cat4 | 1 |
| 4 | Cat5 | 0 |
+----+-----+-------+

note that matches must be of both characteristic so IN statements don't work.

So, any ideas because I've spent hours on this and got nowhere..

 

[Ladforce]

I dont understand this how do you get to your result tables from the tokens your searching for. maybe its just too early for me.

 

[likwid]

Thanks for reply.

Probably easiest if i give you an sql dump

SET NAMES latin1;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE `cats` (
`id` int(10) unsigned NOT NULL auto_increment,
`name` varchar(100) NOT NULL,
`des` varchar(100) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

insert into `cats` values('1','cat1','Cat desc 1'),
('2','cat2','Cat desc 2'),
('3','cat3','Cat desc 3'),
('4','cat4','Cat desc 4'),
('5','cat5','Cat desc 5'),
('6','cat6','Cat desc 6'),
('7','cat7','Cat desc 7');

CREATE TABLE `items` (
`id` int(10) unsigned NOT NULL auto_increment,
`name` varchar(100) NOT NULL,
`des` varchar(100) NOT NULL,
`catId` int(10) unsigned default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

insert into `items` values('1','item 1','item desc 1','2'),
('2','item 2','item desc 2','3'),
('3','item 3','item desc 3','7'),
('4','item 4','item desc 4','3'),
('5','item 5','item desc 5','5'),
('6','item 6','item desc 6','6'),
('7','item 7','item desc 7','1');

CREATE TABLE `itemToks` (
`itemId` int(10) unsigned NOT NULL,
`tok` varchar(100) NOT NULL,
PRIMARY KEY (`itemId`,`tok`)
) ENGINE=innoDB DEFAULT CHARSET=latin1;

insert into `itemToks` values('1','green'),
('1','large'),
('1','round'),
('2','small'),
('2','yellow'),
('3','blue'),
('3','small'),
('4','red'),
('4','small'),
('5','large'),
('5','orange'),
('5','square'),
('6','medium'),
('6','pink'),
('6','triangular'),
('6','yellow'),
('7','massive'),
('7','purple'),
('7','round'),
('8','brown'),
('8','tiny');

SET FOREIGN_KEY_CHECKS = 1;

and the query I have so far

SELECT c.id
     , c.name
     , COALESCE(matches.cnt, 0) AS count
  FROM cats AS c
LEFT OUTER
  JOIN ( SELECT COUNT(*) as cnt
              , id
           FROM items as i
         INNER
           JOIN itemToks as it
             ON it.itemId = i.id
		  WHERE it.tok in ('green', 'round', 'small')
          GROUP
             BY id ) AS matches
    ON c.id = matches.id

This works except for the IN statement. In this example I need that the item have tokens 'green', 'round' AND 'small' rather than 'green', 'round' OR 'small'. So the outer join select is wrong and I need to change it.

Any ideas?

 

[likwid]

This was a working query in the end if anyone is interested. Depending on the number of tokens only ct > * needs be adjusted.

SELECT c.name, COUNT(X.name) FROM cats c
LEFT JOIN
(
  SELECT i.name, i.catid, COUNT(*) as ct
  FROM items i
  INNER JOIN itemtoks t ON i.id = t.itemid AND t.tok IN ('green','round','large')
  GROUP BY i.name
  HAVING ct > 2
) as X
ON c.id = X.catid
GROUP BY c.name