FuelPHP Forums

ilNotturno

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)
            ->related('contents')
                ->where('contents.published', '1')
                ->order_by('contents.published_at', 'desc')
            ->get_one();

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!

Harro

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.

ilNotturno

It doesn't work ... I've tried but the problem persists... Any other idea?

Syntaxlb

I think it's a SQL problem

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

HAVING COUNT(contents.id) = 0 OR COUNT(contents.id) > 0

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

Harro

I think with chaining you can, but I have never used it.

ilNotturno

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.

Harro

method chaining:

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

But I checked, ORM does have group_by(), it does not have having().

ilNotturno

Ah ok, I didn't understood :-).
Thanks for help, I'll try to find another way.

muhlegg

"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.

Harro

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.

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.

Howdy, Stranger!

Categories

In this Discussion