DataMapper - Counting related items

[eluser]NeilyM[/eluser]
Hi guys

I have a question about counting related items when using Datamapper. The following hopefully illustrates what I'm trying to do because my own code would be pretty meaningless without the context. This example has the same "has_many" and "has_one" releationships as mine.

Country has_many Person
Continent has_many Country
Country has_one Continent

I have a Continent and I need to know how many people are in it.

$continent = new Continent;
$continent->get_by_id(1);
$count = $continent->where_related('country/person')->count();

... is what I think I need but it doesn't seem to work.

$continent = new Continent;
$continent->get_by_id(1);
$count = $continent->where_related('country/person')->get()->count();

...does seem to work but I think I am right in saying it loads all the Person records and is therefore not an ideal solution.

Any ideas anyone?

Thank you once again.

Cheers,

NeilyM

[eluser]WanWizard[/eluser]
Have you checked the manual? It has a section on counting related items:

Code:

$continent = new Continent;
$continent->get_by_id(1);
$count = $continent->country->person->count();

As always, if the result is not what you expect it to be, dump the query using check_last_query(). You should be able to deduct from the generated SQL what is wrong with your statement.

[eluser]NeilyM[/eluser]
Yes, I actually know the manual by heart, studied that page, tried various ways to get it and failed.

I guess I didn't try hard enough.

Cheers,

NeilyM

[eluser]NeilyM[/eluser]
Ah - it's the wrong way round!

This seems to work:

$continent_id = 1;
$person = new Person;
$count = $person->where_related('country/continent', 'id', $continent_id)->count();

Cheers,

NeilyM

[eluser]WanWizard[/eluser]
Can you do me a favour, and tell me what query is generated by

Code:

$count = $continent->country->person->count()

Just add a $continent->check_last_query() after this line. I'm curious to why it doesn't work...

[eluser]NeilyM[/eluser]
Sure. check_last_query() generates this:

SELECT COUNT(`countries`.`person_id`) AS `numrows`
FROM (`countries`)
WHERE
`id` IS NULL
AND `person_id` IS NOT NULL

[eluser]WanWizard[/eluser]
That is odd.

Are you sure your models and relations are defined properly? If a person has only one country, there should be a country_id in the persons table and countries.person_id does not exist.
And the count query should be generated on the junction table.

This

Code:

$continent = new Continent(1);
$count = $continent->county->count();

should generate

Code:

SELECT COUNT (`continents_countries`.`country_id`) AS `num_rows' FROM (`continents_countries`) WHERE `continent_id` = 1;

When multiple relations are chained, I expect to see JOIN's in this query.

[eluser]Anestetikas[/eluser]
I have a little bit different problem. Im just starting using datamapper.
Example:
tables:

Code:

bookshelfs(id, name)

books(id, bookshelf_id, title, pagecount)

poems(id, book_id, name, length...)

I want to list of bookshelfs:

Bookshelf number, bookCount, poemCount

As I imagine MVC and ci, i need to generate a list of bookshelfs and pass it to view, i do:

Code:

$shelfs = new Boookshelf();
$shelfs->include_related_count('book');

at this point everything is good, I can pass $shelfs to a view and with foreach loop I can output book_count as a number of books for shelf.

If i do

$shelfs->include_related_count('poem');

I get an error

Unable to relate bookshelf with poem.

In my bookshelf model:

Code:

$has_many = array('book');

book model:

Code:

$has_one = array('bookshelf');  $has_many = array('poem');

In poem model:

Code:

$has_one = array('book');

I can bypass this by starting to ->get() stuff in the view but its the wrong way to do it as I suppose.
My main goal is to show how many poems does the bookshelf contain. What am i missing here?



GOT IT !!!
This stuff is so awesome!
Did the wrong approach.

[eluser]phobeous[/eluser]
Hello, I know it's been long since this thread was last replied, but I'm facing the same issue as first post. My relationships:

expedientes --- 1:N --> servicios --- 1:N --> adjuntos

adjuntos.servicio_id exists
servicios.expediente_id exists

Models are correctly related

Code:

class Expediente extends DataMapper
{
   public $has_many = array('servicio');
   //...
}
//...
class Servicio extends DataMapper
{
   public $has_one = array('expediente');
   public $has_many = array('adjunto');
   //...
}
//...
class Adjunto extends DataMapper
{
   public $has_one = array('servicio');
   //...
}

All my models have $table and $model overriden because inflector on spanish may not work properly. Database has rows on tables that relate items. If I do the count from the last side of the oneToMany relation (adjuntos) it works:

Code:

$adjuntos = new Adjunto();
$adjuntos->where_related('servicio/expediente', 'id', 1);
echo $adjuntos->count(); // Echoes the correct number of related adjuntos for servicios on expediente with id 1.

I will use the working option, but it would be very nice (and cool!) to perform the query this other way:

Code:

$expedientes = new Expediente(1);
$adjuntos_count = $expediente->servicio->adjunto->count();

Cheers.

[eluser]WanWizard[/eluser]
You can't do that, because 'servicio' is a collection due to the 1:N relation. So on what do you want to run the count? On all results? That can potentionally be a lot of queries.

You could see if include_related_count('servicio/adjunto') does the job?