Love Fuel?    Donate

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.
Relation many to many filter WHERE IN
  • MaxZnMaxZn
    http://dumpz.org/353818/ - Throws an error. I think this is not what is needed in my case. ON this alias WHERE to use interior JOIN. Thus there is no need to open a new "WHERE" clause as is already open "ON" Example: ON (tbl_one.id = tbl_two.id) AND (bla-bla OR bla-bla)
  • HarroHarro
    I can image that throws a SQL error, that's complete rubbish.

    You're 'or_on" clauses are selection criteria, and should be WHERE clauses.
  • MaxZnMaxZn
    You're 'or_on" clauses are selection criteria, and should be WHERE clauses. - I don't understand. Can be an example?
  • HarroHarro
    Like so: http://www.snipr.it/~tp
  • MaxZnMaxZn

    This example generates a request:


    ELECT
    DISTINCT * FROM `frocks` JOIN `frocks_sizes` ON (`frocks`.`id` =
    `frocks_sizes`.`frock_id`) WHERE `frocks`.`brand_id` IN (19) OR
    `frocks_sizes`.`size_id` IN (7)

    This is not what I need. "WHERE clause" after "ON", it's like a "WHERE" clause after "WHERE clause"

  • HarroHarro
    Then I'm lost to what you're trying to do.

    You specify "frocks.brand_id = 19" and "frocks_sizes.size_id = 7", which for me are selection criteria, that have nothing to do with the join.

    From what I understood, you want "All frocks from the selected brands, in the selected sizes".
    That doesn't require a distinct, and not an OR but an AND.

    If this is not what you want, can you describe in words what your query is supposed to do?
  • MaxZnMaxZn
    It should filter dresses in size( the size can be more than one ) by brand, etc. The problem is not in the request, but the fact that the DB Builder does not work correctly. He ignores DISTINCT.
  • HarroHarro
    Accepted Answer
    Examine what this query does:

    It joins 'frocks' with 'frocks_sizes', which as it's a one-to-many will cause the 'frocks' part to be repeated. Then you select '*', meaning all fields from both tables. Then you do a DISTINCT, which will return only unique rows.

    You probably assume that this will cause only a single 'frocks' row to be returned. It doesn't. DISTINCT only filters duplicate rows, meaning ALL selected columns must be equal. Which is not the case on a join like this. So DISTINCT works, it just doesn't do what you think it does...

    If you change it to "SELECT frocks.*" it will probably work.
  • MaxZnMaxZn
    http://dumpz.org/354516/ - I did this subquery. This works correctly, DISTINCT, OFFSET, LIMIT all works as it should. Thank you Harro, you've helped a lot.
Add a Comment

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Apply for Membership

Categories

In this Discussion