Love Fuel?    Donate

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.
Where clause in a relatd model
  • Hi guys,
    I've a problem retrieving values from db.

    I have a many to many relation between Archives and Contents. I need to get an archive and all related contents, but only if "contents.published is equal to 1".
    Here is my code:

            $archive = Model_Archive::query()
                ->where('code', $code)
                    ->where('contents.published', '1')
                    ->order_by('contents.published_at', 'desc')

    The problem is this: if I have only NOT published contents, I want to retrieve the archive and I want the "contents" property to be empty. With that query the entire $archive value is empty.

    Is it possible to achieve it? Thanks!
  • Instead of a get_one(), try rows_limit(1)->get(); $archive = reset($archive).

    Related queries are generated using subqueries, which in combination with get_one() might not give you the result you expect.
  • It doesn't work ... I've tried but the problem persists... Any other idea?
  • I think it's a SQL problem

    For example if you add at the end of the SQL Query :


    It's work. But i think you can't use "having" with model query() function ? 

  • I think with chaining you can, but I have never used it.
  • Thanks for the responses guys.
    Harro, what do you mean with "chaining"? Can you give e an URL so I can try?

    I have a "B plan", to cycle the results after the query and unset contents I don't need, but I want to do it with a single query.
  • method chaining:

    Model::query()->where()... instead of Model::find('all', array(...))

    But I checked, ORM does have group_by(), it does not have having().
  • Ah ok, I didn't understood :-).
    Thanks for help, I'll try to find another way.
  • "Related queries are generated using subqueries, which in combination with get_one() might not give you the result you expect." This sound a bit confusing. Haven't seen anything about this in the docs. 
  • HarroHarro
    Accepted Answer
    The ORM will always try to construct consistent resultsets.

    Which means that if you have 1 parent and 10 children, and you request a limit(5), in a normal join you will only get 5 of the 10 child records back. With ORM, you will still get all 10 children back.

    A get_one() is basically a limit(1)->get(), so it will follow this rule.

    However, if you use rows_limit() instead of limit(), the limit will be applied to the outer query (the join itself), producing a result you would get when you would have manually written the join.

    Just try the two, enable the profiler, and database profiling, and compare the SQL of the two queries produced.

Howdy, Stranger!

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

In this Discussion