Thread

Ah Phil, that just means you are loved. 😉

I’ve searched this thread and haven’t found anything so apologies if this has already been discussed.

Example: I have two models, Customer & Brand. They have a many_to_many relationship.

I want to run a query that returns all Brands and has a flag/count if each brand is already linked with a given Customer. The query should also be able to order Brands by those already linked/not linked.

I’m looking for something like include_related_count but i need to be able to specify a specific related record and also return records not related.

Thanks.

Hello,

I’m wondering how it is possible to set WHERE condition when using include_related_count in such case:

$keywords = new Keyword();<br /> $keywords->include_related_count(‘image’);<br /> $keywords->where(‘image_count <’, 3);<br /> $keywords->get();

Is this possible?

@The Hamburgler
I don’t know how you would do that with normal SQL, but I’m guessing you would need to build up a subquery.  DMZ support subqueries, so it’s just a matter of being creative.  The subquery example in the manual is actually pretty close to what you want.

@introvert
The (poorly named on my part) include_related_count method is a SELECT element, and therefore cannot be used in the WHERE part of a query.  (This is part of the SQL design.)  However, it’s simply a subquery, so you should be able to build up your own subqery and use it in a where statement.  If necessary, you can look at the source code for the include_related_count method to see how to build your own subquery.

Hi guys,

I have two models, Post and Category. Post have many relationship on Category, and Category have many relationship on Post.

Is there a way to get all post without Category?

I tried (without any hope) to make that:

$oCat = new Category();
$oCat->get();

$oPost = new Post();
$oPost->where_not_in_related($oCat);

Thanks 😊

Nothing for that ? 😊

@The Hamburgler
I don’t know how you would do that with normal SQL, but I’m guessing you would need to build up a subquery.  DMZ support subqueries, so it’s just a matter of being creative.  The subquery example in the manual is actually pretty close to what you want.

@introvert
The (poorly named on my part) include_related_count method is a SELECT element, and therefore cannot be used in the WHERE part of a query.  (This is part of the SQL design.)  However, it’s simply a subquery, so you should be able to build up your own subqery and use it in a where statement.  If necessary, you can look at the source code for the include_related_count method to see how to build your own subquery.

Do you have any idea how should the subquery?

The basic query is:

SELECT `keywords`.*, (SELECT COUNT(*) AS count FROM (`images`) LEFT OUTER JOIN `keywords` `keywords_subquery` ON `keywords_subquery`.`id` = `images`.`keyword_id` WHERE `keywords_subquery`.id = `keywords`.`id`) AS image_count FROM (`keywords`)

If I add:

WHERE image_count < 2

It won’t work.

Thanks,

I’ve had a play with subqueries an this seems to do the trick.

The following code will return all brands.
The records are ordered so brands related with out customer will appear first.

$cust = new Customer();
$active_id = 3;
                    
// Select count for related customers with given id
$cust->select_func('COUNT', '*', 'count');
$cust->where('id', $active_id);
$cust->where_related('brand', 'id', '${parent}.id');

$brand = new Brand();
            
// Add to the brand query
$brand->select_subquery($cust, 'is_linked');
$brand->select('*');
                    
// order so linked brands appear at the top
$brand->order_by('is_linked', 'DESC');

$brand->get();

Here’s a semantics question for you!

If you were saving both images and video information to a table, what would you call the model?

Really it should be “media”, but while that is a plural, it doesn’t feel right to call the singular a “medium”?!

What have other people used for this. “Assets”, or “Attachments”, perhaps?

@Daniel H
Well, I guess it depends on the purpose of the information.  If it’s correct, I like attachments (and it lends itself to other formats, too).

Otherwise, assets is a good name.  Or just call it media and call the table medias 😛

It’s for a portfolio management system, where each portfolio can have images and videos, so I guess assets will probably be best here. Thanks!

I have a question:

If I use the “get_paged_iterated” function to get my resultset I can’t use the “include_join_fields” function, right?

Thanks for a short answer.

@Benedikt
It should work just fine.  Those are simply added to the result of the query.

The *_iterated methods have basically no limitations as long as you are simply looping over the results.

Hm, it seems not to work for me. I tried

$n->include_join_fields->get_paged_iterated();

But it’s the same result like

$n->get_paged_iterated();

If I run

$n->include_join_fields->get();

it works just fine. If you have time, maybe you can check if this works for you, then seems I did something wrong else where.

@Benedikt
First, it’s a function, so you are missing parentheses in your example.  😊

And it should work.  I just walked through the code to see how it is called, and the same methods are used to build the query for get and get iterated.  The only real difference is what is done with the result of the query.

So, add the parentheses, and use the profiler to check the queries being run.  Maybe you can find out what’s not working then.  Also, when testing, try get_iterated, instead of get_paged_iterated, just to see if there is a difference.

The parentheses are there, I just forgot to type them in my quote. Sorry.

My mistake was that I used “where_related” instead of $n->$x->...

Now it’s working. Thanks.