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

    I wrote a site on Fuelphp at the last moment and was stuck with a filter. The filter on the parameters of the dresses, it can be seen here see in FireBUG http://maxdevelopment.ru/catalog I have used many-to-many relationship for tables sizes and frocks. I need filter dresses including in size. Filtering is not correct, when filtered by size. Please help me in this.

    My code - http://dumpz.org/349785/


  • HarroHarro
    I'm not sure the options array notation of query() supports WHERE IN. Might that be the issue?
  • MaxZnMaxZn
    It is possible, but how do differently without an array? I need filtering.
  • HarroHarro
    Accepted Answer
    Use the chaining method, like you do for the remainder of the query.

    Start your code with:
    $query = $this->getOrmFrock()->query();
    then when you need to add something:
    if ( ! empty($something)) $query->where('field', 'IN', $something);
    And end with
    $data['frocks'] = $query->limit(16)->get();
  • MaxZnMaxZn
    I try it. Thanks a lot, Harro.
  • MaxZnMaxZn

    http://dumpz.org/350759/

    Then the solution of which work correctly with orm+limit+offset+2ordering+where+in may to someone it will help. 

  • HarroHarro
    That doesn't make much sense. Why get all records, and then throw everything you don't need away?
  • MaxZnMaxZn
    I know that it is not good. Set a limit of 50 entries. But array_slice remained, as in my case, the limit() - rows_limit() - offset() - rows_offset() does not work properly. For example when using rows_limit() - rows_offset() stops working sorting, and if limit() - offset() is ignored limit - indent.
  • HarroHarro
    Accepted Answer
    I don't understand this.

    rows_limit() only forces the LIMIT to be placed on the outer query instead of the subquery, it will not influence any ORDER_BY clause.

    You have to understand that the ORM is an object relationship manager, not a query builder. So there are limits to what you can query with ORM's own methods, it isn't designed for any arbitrary query.

    If you have have complex queries, it's better to just use the DB class, and use as_object('Mymodel') to convert the result of the query into ORM objects.
  • MaxZnMaxZn
    Thank you. I really was wrong. I thought that ORM allows us to build complex and sophisticated queries, now I will use the DB for that. But I would like to specify, as in the DB looks where-in in a nested context many-to-many?
  • HarroHarro
    Accepted Answer
    With DB you just construct your own query, so you have have it any way you want.
  • MaxZnMaxZn
    Written request to the DB, but there is a problem - select distinct игнорируется. http://dumpz.org/353550/ Tell me please what is my mistake?
  • MaxZnMaxZn
    Written request to the DB, but there is a problem - select distinct is ignored. http://dumpz.org/353550/ Tell me please what is my mistake?
  • MaxZnMaxZn
    Is built here's a request http://dumpz.org/353556/ Checked it via the console mysql it works, and distinct is not ignored.
  • HarroHarro
    Enable the profiler or use 
    echo DB::last_query();
    to check what SQL is exactly generated.


  • MaxZnMaxZn

    this request there is generated http://dumpz.org/353556/, I just removed unnecessary.) Here is one in a single request from a Profiler:
    _query =
    'SELECT
    DISTINCT * FROM `frocks` JOIN `frocks_sizes` ON
    (`frocks_sizes`.`frock_id` = `frocks`.`id`) HAVING
    `frocks_sizes`.`size_id` IN (7) OR `frocks`.`brand_id` IN (19)'
  • HarroHarro
    Accepted Answer
    Query looks ok.

    What happens if you paste this into something like phpmyadmin or the commandline sql tool?

    p..s if you don't have a GROUP BY, I would use WHERE instead of HAVING...
  • MaxZnMaxZn
    What happens if you paste this into something like phpmyadmin or the commandline sql tool? - I checked in the terminal mysql, query works correctly.
  • MaxZnMaxZn
    p..s if you don't have a GROUP BY, I would use a WHERE clause instead of HAVING... - I tried it, but didn't work
  • MaxZnMaxZn
    if this http://dumpz.org/353628/ distinct works
  • HarroHarro
    Accepted Answer
    so the problem is in the second or_having.

    As I wrote, you have to be careful when you use HAVING instead of WHERE. WHERE is a result selection (so it runs while selecting data for the result), while HAVING is a result filter, which is applied after the results have been selected.

    Try this instead: http://www.snipr.it/~tn
  • MaxZnMaxZn
    "where" not helps http://dumpz.org/353655/
  • MaxZnMaxZn
    http://dumpz.org/353746/ - This works, and distinct is not ignored. Don't understand why is not working through the Builder.
  • MaxZnMaxZn
    http://dumpz.org/353769/ - it also works fine, but not in the Builder
  • HarroHarro
    Accepted Answer
    1.5/develop supports multiple ON's on a JOIN, that is not available in 1.4.
  • MaxZnMaxZn
    I can try to transfer the files to your 1.4? or is it still not ready?
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