Many-to-many exclusive conditions??

[kael.shipman]

Hey all,

 

I've had this problem for a while now and have searched the boards and google and everything and haven't been able to figure it out: I have an images table and a filters table. Each image can belong to multiple filters and each filter can belong to multiple images - standard many-to-many. The problem is that I need to query XREF table with exclusivity, so I need to say "get ONLY images that belong to these filters". My tables look roughly like this:

 

-- 
-- imgs
--

create table `imgs` (
    `imgID` serial PK,
    `img_nm` varchar(30) not null
);



--
-- filters
--

create table `filters` (
    `filterId` serial PK
    `filter_name` varchar(30) not null,
    `filter_val` varchar(30) not null
);



--
-- imgs_filters
--

create table `imgs_filters` (
    `id` serial pk,
    `img_id` int not null,
    `filter_id` int not null
);

 

 

That's a pretty dumbed down version, and structured a bit differently, but it'll get the point across. Please don't nit pick any coding errors. This isn't real code; just concept.

 

So when I query the table with one filter, I'm fine:

 

@mysql_query("select i.imgID from imgs i, imgs_filters f where f.imgID = i.imgID && f.filter_id = 1 limit 0, 25");

 

That'll pull up any images that are in the imgs_filters table with filter_id = 1. However, if I want to apply filter number 1 AND filter number 2, now I've got a problem because I can't say "... where f.imgID = i.imgID && f.filter_id = 1 && f.filter_id = 2 ..." because that's always impossible.

 

I've always solved this by simply putting a string column in the imgs table like this:

 

create table `imgs` (
    `img_id` serial pk,
    `img_nm` varchar(20) not null,
    `filters` varchar(100) not null
);

//Periods go on both the beginning and end of the string so that each number can be referenced by .n.
insert into `imgs` set `img_nm` = 'name', `filters` = '.1.2.';

 

This way, I can write the following query and get the results I want:

 

@mysql_query("select i.imgID from imgs i where i.filters like '.1.' && i.filters like '.2.'");

 

OR

 

I can write an inclusive search with "||", which adds a nice flexibility to the structure.

 

That works great, but it's sloppy as hell. I figure there's gotta be a more table-based approach to this. Any insights?

 

Thanks,

Kael

[kael.shipman]

Alright, so you can hate me for reading the "READ THIS BEFORE POSTING" post AFTER I posted... My MySQL version is 5.0.something. It's not really relevant to my question. Thank you.

[fenway]

Concatenating uids like that is HORRIBLE -- don't even think about doing it.

 

You have a few choices: you can use a IN () clause and then make sure that you find both filter ids "after"... or you could self-join again.  OR you could use an EXISTS subquery.