current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
stack overflow careers
Sign up ×
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them, it only takes a minute:
up vote 0 down vote favorite

I have this SQL query to select some of my offers and it works as expected.

SELECT * FROM `offers` WHERE region='26'

AND EXISTS (
SELECT * FROM `modificator_values` WHERE
    `modificator_values`.`offer_id`=`offers`.`id` AND
     (modificator_values.modificator_id = "5") AND (modificator_values.data = "data1")
) 

AND EXISTS (
SELECT * FROM `modificator_values` WHERE
     `modificator_values`.`offer_id`=`offers`.`id` AND
     (modificator_values.modificator_id = "8") AND (modificator_values.data = "data2")
)

Is there a way to optimize it and do it using ActiveRecord query API?

share|improve this question

1 Answer 1

up vote 0 down vote accepted

Although not using any API, I suggest you use this trick, you only check for existance in both subqueries and could save that 'double' time by doing something like this:

SELECT * FROM offers 
WHERE region='26'

AND (SELECT COUNT(*)
  FROM (
    SELECT modificator_id,COUNT(data) 
    FROM modificator_values 
    WHERE
      modificator_values.offer_id=1 AND
       (((modificator_values.modificator_id = "5") AND (modificator_values.data = "data1")) OR
       ((modificator_values.modificator_id = "8") AND (modificator_values.data = "data2")))
    GROUP BY modificator_id 
    ) s
    ) > 1

I've tested it on this schema:

CREATE TABLE offers 
    (
     id int auto_increment primary key, 
     region varchar(20)
    );
CREATE TABLE modificator_values 
    (
     modificator_id int auto_increment primary key, 
     data varchar(20), 
     offer_id int
    );

INSERT INTO offers VALUES (1,'26');
INSERT INTO modificator_values VALUES(5,'data1',1);
INSERT INTO modificator_values VALUES(8,'data2',1);
share|improve this answer
    
Thanks for the time and the idea. :) – ivs Jun 1 '13 at 18:52
    
No problem, if you think this answers your question please use the V next to it in order to approve it for future users that might come across it, you can also use the arrow up in order to upvote it and mark it as a useful answer. and welcome to Stackoverflow =] – Ron.B.I Jun 1 '13 at 19:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.