0

I have the follow tables:

systems
-----------
id
name
price
online
productid


specifications
-------------
id
systemid
type
componentid
quantity


components
-------------
id
name
brand
type
description

I need to filter across these tables with multiple options. Each system has multiple specifications rows, and each 'specifications' row links to it's corresponding 'components' row.

My issue is this:

I need to be able to filter systems by multiple properties based on the table joins. I've been using code that'll allow me 1 search option, but nothing further:

select
    `systems`.`id`,
    `systems`.`name`, 
    `specifications`.`type` 
from `systems` 
    join specifications 
        on `systems`.`id` = `specifications`.`systemid` 
    join components 
        on `specifications`.`componentid` = `components`.`id`
where 
    `specifications`.`type` = 'cpu' 
    and `components`.`brand` = 'amd'

So, that'll let me do a join where the specifications type is CPU and the brand is AMD, but if I add something else to look for too, like specifications.type ='graphics' AND components.brand = 'nvidia' it just doesn't work. I think this is inherent in the way joins work, as I've said, I'm having trouble articulating the issue here as I'm quite new to these more complex database transactions and would greatly appreciate being pointed in the right direction!

I'm using CodeIgniter as my framework, and I'd like to try and get the bottom of this via MySQL as opposed to doing it in PHP if it's possible - as I'd like a better understanding of what's going on here.

Filip Roséen - refp
  • 62,493
  • 20
  • 150
  • 196
fin
  • 93
  • 1
  • 8
  • 1
    How are you adding `specifications.type ='graphics' AND components.brand = 'nvidia'` to your clause? What results are you getting when you run it? – andrewsi Nov 01 '12 at 16:05
  • Its not related with question but may be useful. I recommend you to rename columns systemid to systems_id. You can easy migrate to DataMapper with foreign key columns named like this (tablename_id). – Kamil Nov 01 '12 at 16:12
  • @andrewsi I was simply adding additional "AND" statements to the bottom, so imagine the above statement with "AND `specifications`.`type` = 'graphics' and `components`.`brand` = 'nvidia'. It seems that Darrrren's answer (below) of grouping the where statements together seems to be heading in the right direction, but isn't working when running the statement with both where clauses. – fin Nov 01 '12 at 16:30

2 Answers2

1

Do you mean to say that

select `systems`.`id`,`systems`.`name`, `specifications`.`type` from `systems`
join specifications on `systems`.`id` = `specifications`.`systemid` 
join components on `specifications`.`componentid` = `components`.`id`
where 
     (`specifications`.`type` = 'cpu' AND `components`.`brand` = 'amd') OR
     (`specifications`.`type` = `graphics` AND `components`.`brand` = `nvidia`)

Does not work?

What about something like this

SELECT S.`id`, S.`name`, P.`type` FROM `systems` S 
JOIN `specifications` P ON S.`id` = P.`systemid`
WHERE S.`id` IN (

    SELECT S2.`systemid` AS id FROM `specifications` S2
    JOIN `components` C2 ON S2.`componentid` = C2.`id`
    WHERE S2.`type` = 'cpu' AND c2.`brand` = 'amd'
) AND S.`id` IN (

    SELECT S3.`systemid` AS id FROM `specifications` S3
    JOIN `components` C3 ON S3.`componentid` = C3.`id`
    WHERE S3.`type` = 'graphics' AND c3.`brand` = 'nvidia'
)
Darrrrrren
  • 5,968
  • 5
  • 34
  • 51
  • I didn't know you could effectively 'group' where statements. The above works for retrieving all systems that have either an AMD CPU __or__ NVIDIA graphics, but I wanted it to use both, I changed the 'OR' in there to an 'AND' but it won't produce a result. – fin Nov 01 '12 at 16:28
  • Ha! We seem to have hit upon the same fix at the same time - yours being a tad more elegantly typed than mine. I've asked a few questions about doing it this way in my answer, any help with those would be appreciated :) – fin Nov 01 '12 at 17:43
  • Our queries are not exactly the same - since you are nesting more complex queries than I am - note that in each subquery of yours, you will be performing two joins - table joins are expensive. Each subquery of my solution will only perform one join. – Darrrrrren Nov 01 '12 at 17:48
0

Ok, I've got it working, by having each query run as a subquery.

So it returns all the IDs of systems that have an AMD processor, to a conditional IN clause of finding all the systems that have an NVIDIA graphics card.

SELECT  `systems`.`id` ,  `systems`.`name` ,  `specifications`.`type` 
FROM  `systems` 
JOIN specifications ON  `systems`.`id` =  `specifications`.`systemid` 
JOIN components ON  `specifications`.`componentid` =  `components`.`id` 
WHERE (
    `specifications`.`type` =  'graphics'
    AND  `components`.`brand` =  'nvidia'
    )
AND (
    `systems`.`id` IN (
          SELECT  `systems`.`id` 
          FROM  `systems` 
          JOIN specifications ON  `systems`.`id` =  `specifications`.`systemid` 
          JOIN components ON  `specifications`.`componentid` =  `components`.`id` 
     WHERE (
          `specifications`.`type` =  'cpu'
          AND  `components`.`brand` =  'amd'
          )
     )
 )

Programmatically, I find it quite cumbersome, and I'm not sure how it stacks up efficiency-wise - being a largely self-taught programmer I'm always trying to make sure I'm doing things the "right" way. Can anyone see any issues with running it this way? Would it be better to have CodeIgniter return that set of IDs instead?

Bare in mind, that this question was simplified somewhat, and it will eventually contain several subqueries - though the site itself will never be under a massive load.

fin
  • 93
  • 1
  • 8
  • I don't believe using CodeIgniter's ActiveRecord class for this query is the correct approach, however if you really want to try and hack subqueries into ActiveRecord, there's a thread on it [here](http://stackoverflow.com/questions/6047149/subquery-in-codeigniter-active-record) – Darrrrrren Nov 01 '12 at 17:50