DMZ 1.7.1 (DataMapper OverZealous Edition)

[eluser]introvert[/eluser]
[quote author="OverZealous" date="1275717205"]@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.[/quote]

Do you have any idea how should the subquery?

The basic query is:

Code:

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:

Code:

WHERE image_count < 2

It won't work.

[eluser]The Hamburgler[/eluser]
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.

Code:

$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();

[eluser]Daniel H[/eluser]
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?

[eluser]OverZealous[/eluser]
@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

[eluser]Daniel H[/eluser]
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!

[eluser]Benedikt[/eluser]
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.

[eluser]OverZealous[/eluser]
@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.

[eluser]Benedikt[/eluser]
Hm, it seems not to work for me. I tried

Code:

$n->include_join_fields->get_paged_iterated();

But it's the same result like

Code:

$n->get_paged_iterated();

If I run

Code:

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

[eluser]OverZealous[/eluser]
@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.

[eluser]Benedikt[/eluser]
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.