Querying an associated table

[thejoelinux]

Hi,

I'm not sure what i want to do is supported by this plugin. I've made a users' list in datatable, it worked. But let's say there a country_id in users table; how could I query and display the associated country_name (found in the country table) ?

Now about code organisation; if i want to make a datatable for user_objects: should it be in the UserObjects controller ? or a method Objects in the users controller ? In either way, how do i query only the objects that belong to the specified user ? (I tried the Objects method in the users controller, I didn't find a way to do so).

Thank very much for your help,

[thejoelinux]

About the first part of the question (and maybe it's also the solution for the second part)

If you want to display the country, you got to define a custom finder with the method finder(), implement this finder in the UsersTable, something like

public function findByCountry(\Cake\ORM\Query $query, array $options)
    {
        $query
            ->select(['id', 'name', 'country_id'])
            ->contain([
                'Countries' => ['fields' => ['Countries__name' => 'Countries.name']],
            ]);

        return $query;
    }

Reference in your config with

->finder('ByCountry')
->column('Countries.name', ['label' => 'Country name'])

And finally in the template

foreach ($results as $result)
{
    $this->DataTables->prepareData([
        $result->id,
        $result->country->name,
        $result->created,
        $result->modified,
        $this->Html->link('Edit', ['action' => 'edit', $result->id])
    ]);
}
echo $this->DataTables->response();

[thejoelinux]

@allanmcarvalho Please don't bother answering this issue; I will soon propose a documentation patch to address those two use cases.