1

I have to Tables: Users hasmany Memberhip. I would like to build a query to get ALL Users but EACH User should only contain the FIRST Membership (after ordering).

$users = $this->Users->find()->contain([
  'Membership' => function ($q) {
    return $q->order([
      'year' => 'ASC'
    ])->limit(1);
  },
  'Countries',
  'Board',
]);

Seems good so far. The problem is, that this query only gets a single Membership alltogether. So in the end of all Users that are beeing fetched, only one User has one Membership.

How do I get CakePHP to fetch ONE Membership for EACH User?

Thanks!

repher
  • 13
  • 1
  • 3
  • 2
    possible duplicate of [Loading Associated Model Data in Cakephp3](http://stackoverflow.com/questions/30241975/loading-associated-model-data-in-cakephp3) – ADmad Aug 13 '15 at 17:04

3 Answers3

1

Sort key for hasOne option doesn't exist (see : CakePhp3 issue 5007)

Anyway I had the same problem and thanks to ndm : here his solution

Community
  • 1
  • 1
Said Chada
  • 11
  • 2
1

I ended up using this:

$this->hasOne('FirstPhoto', [
        'className' => 'Photos',
        'foreignKey' => false,
        'conditions' => function (\Cake\Database\Expression\QueryExpression $exp, \Cake\ORM\Query $query) {
            $subquery = $query
                ->connection()
                ->newQuery()
                ->select(['Photos.id'])
                ->from(['Photos' => 'photos'])
                ->where(['Photos.report_id = Reports.id'])
                ->order(['Photos.id' => 'DESC'])
                ->limit(1);

            return $exp->add(['FirstPhoto.id' => $subquery]);
        }
    ]);

Reference: How to limit contained associations per record/group?

Community
  • 1
  • 1
Melvin
  • 3,421
  • 2
  • 37
  • 41
0

You can do this is by creating another association (Make sure you don't include a limit you don't need one as the hasOne association creates the limit):

$this->hasOne('FirstMembership', [
'className' => 'Memberships',
'foreignKey' => 'membership_id',
'strategy' => 'select',
'sort' => ['FirstMembership.created' => 'DESC'],
'conditions' => function ($e, $query) {
    return [];
}]);

This works for limiting to 1 but if you want to limit to 10 there still seems to be no good solution.

Westy
  • 93
  • 7