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
  • 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 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 -

  • I'm not sure the options array notation of query() supports WHERE IN. Might that be the issue?
  • 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();
  • I try it. Thanks a lot, Harro.

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

  • That doesn't make much sense. Why get all records, and then throw everything you don't need away?
  • 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.
  • 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.
  • Written request to the DB, but there is a problem - select distinct игнорируется. Tell me please what is my mistake?
  • Written request to the DB, but there is a problem - select distinct is ignored. Tell me please what is my mistake?
  • Is built here's a request Checked it via the console mysql it works, and distinct is not ignored.
  • Enable the profiler or use
    echo DB::last_query();
    to check what SQL is exactly generated.

  • this request there is generated, I just removed unnecessary.) Here is one in a single request from a Profiler:
    _query =
    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...
  • What happens if you paste this into something like phpmyadmin or the commandline sql tool? - I checked in the terminal mysql, query works correctly.
  • 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
  • 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:
  • - This works, and distinct is not ignored. Don't understand why is not working through the Builder.
  • - 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.
  • I can try to transfer the files to your 1.4? or is it still not ready?

Howdy, Stranger!

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

In this Discussion