Order linked table in a has many through relation association (CakePhp 2)

samclauw · December 16, 2017, 9:02am

Hi there, ready for my second post here!

I’ll explain my question with an existing situation. I have 3 tables that I use to show animal information on a list page and a detail page. You can see a working example on my staging website.

I use 3 database tables to hold this information.

Table 1: animals

id
name (string)
sequence (integer)
…

=> ‘Name’ data in this table can be ‘elephant’, ‘lion’, ‘giraffe’, …
=> I use ‘sequence’ to order the animals in a logic way.

Table 2: animal_properties

id
name (string)
sequence (integer)
…

=> ‘Name’ data in this table can be ‘status’, ‘living habitat’, ‘weight’, ‘enemy’, …
=> I use ‘sequence’ to order the properties in a logic way.

Table 3: animal_property_links

id
animal_id (integer, foreign key)
animal_property_id (integer, foreign key)
name (string)
…

=> ‘Name’ data in this table can be ‘threatened’, ‘savannah’, ‘120 kilogram’, ‘human’, …

My question is: how can I sort the animals by sequence and sort their connected animal_property_links by the sequence of the animal_properties? It should be automatically done by this simple (test)code in the App Controller:

<?php

class AppController extends Controller {
    public function beforeRender()
    {
        $this->loadModel('Animal');
        
        $animal = $this->Animal->find('all', array(
            'recursive' => 1,
            'fields' => array(
                'Animal.id',
                'Animal.name'
            )
        ));
        
        debug($animal);
    }
}

?>

But when I request the above code with my Model setup, I get this error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘AnimalProperty.sequence’ in ‘order clause’

I know for sure it’s very important to think about the model structure so I spend a while to do the brainstorm stuff. Here is my model setup.

Model ‘Animal’

<?php

App::uses('AppModel', 'Model');

class Animal extends AppModel
{
    public $order = array(
        'Animal.sequence' => 'ASC',
        'Animal.id' => 'ASC'
    );
    
    public $hasMany = array(
        'AnimalPropertyLink' => array(
            'className' => 'AnimalPropertyLink',
            'foreignKey' => 'animal_id',
            'conditions' => array(
                'AnimalPropertyLink.deleted' => null
            ),
            'order' => array(
                'AnimalProperty.sequence ASC',
                'AnimalProperty.id ASC'
            )
        )
    );
}

?>

Model ‘AnimalProperty’

<?php

App::uses('AppModel', 'Model');

class AnimalProperty extends AppModel
{
    public $order = array(
        'AnimalProperty.sequence' => 'ASC',
        'AnimalProperty.id' => 'ASC'
    );
    
    public $hasMany = array(
        'AnimalPropertyLink' => array(
            'className' => 'AnimalPropertyLink',
            'foreignKey' => 'animal_property_id',
            'conditions' => array(
                'AnimalPropertyLink.show' => 'Y',
                'AnimalPropertyLink.deleted' => null
            ),
            'order' => array(
                'AnimalProperty.sequence ASC',
                'AnimalProperty.id ASC'
            )
        )
    );
}

?>

Model ‘AnimalPropertyLink’

<?php

App::uses('AppModel', 'Model');

class AnimalPropertyLink extends AppModel
{
    public $order = array(
        'AnimalProperty.sequence' => 'ASC',
        'AnimalProperty.id' => 'ASC'
    );
    
    public $belongsTo = array(
        'Animal' => array(
            'className' => 'Animal',
            'foreignKey' => 'animal_id',
            'conditions' => array(
                'Animal.show' => 'Y',
                'Animal.deleted' => null
            ),
            'order' => array(
                'Animal.sequence ASC',
                'Animal.id ASC'
            )
        ),
        'AnimalProperty' => array(
            'className' => 'AnimalProperty',
            'foreignKey' => 'animal_property_id',
            'conditions' => array(
                'AnimalProperty.deleted' => null
            ),
            'order' => array(
                'AnimalProperty.sequence ASC',
                'AnimalProperty.id ASC'
            )
        )
    );
}

?>

Can anyone find my mistake that causes the error?
Thanks for reading en hopefully for helping too

Sam

samclauw · January 2, 2018, 7:44pm

Okay, I learned some new things after I’ve posted my question, but still I don’t get the desired result. I do know that…

… you can only order a hasOne and a belongsTo association with the default find method. So you can’t order a hasMany association. In my case, I have a has many through association that defines two hasMany associations.

… you better work with the containable behavior to minimize the query output. So I’ve made a new attempt to put the animals with their property links and their associated properties in one output array. That’s my code:

  $this->loadModel('Animal');
  
  $findOptions['recursive'] = -1;
  
  $findOptions['fields'] = array(
      'Animal.id',
      'Animal.name',
      'Animal.sequence'
  );
  
  $findOptions['order'] = array(
      'Animal.sequence' => 'ASC'
  );
  
  $findOptions['contain'] = array(
      'Area' => array(
          'fields' => array(
              'Area.name'
          ),
      ),
      'AnimalPhoto' => array(
          'fields' => array(
              'AnimalPhoto.name',
              'AnimalPhoto.alt',
              'AnimalPhoto.title',
              'AnimalPhoto.sequence'
          ),
          'order' => array(
              'AnimalPhoto.sequence' => 'ASC'
          ),
          'limit' => 1
      ),
      'AnimalPropertyLink' => array(
          'fields' => array(
              'AnimalPropertyLink.id',
              'AnimalPropertyLink.name'
          ),
          'order' => array(
              'AnimalProperty.sequence' => 'DESC'
          ),
          'AnimalProperty' => array(
              'fields' => array(
                  'AnimalProperty.id',
                  'AnimalProperty.name',
                  'AnimalProperty.sequence'
              )
          )
      )
  );
  
  $animal = $this->Animal->find('all', $findOptions);

This is my output, allmost there:

    array(
    	(int) 0 => array(
    		'Animal' => array(
    			'id' => '22',
    			'name' => 'Amoerluipaard',
    			'sequence' => '1'
    		),
    		'Area' => array(
    			'name' => 'India',
    			'id' => '5'
    		),
    		'AnimalPhoto' => array(),
    		'AnimalPropertyLink' => array(
    			(int) 0 => array(
    				'id' => '124',
    				'name' => 'Ernstig bedreigd',
    				'animal_property_id' => '1',
    				'animal_id' => '22',
    				'AnimalProperty' => array(
    					'id' => '1',
    					'name' => 'Status',
    					'sequence' => '2'
    				)
    			),
    			(int) 1 => array(
    				'id' => '125',
    				'name' => 'Oost-Rusland (Primorsky), Noord-Oost China en Noord-Korea',
    				'animal_property_id' => '2',
    				'animal_id' => '22',
    				'AnimalProperty' => array(
    					'id' => '2',
    					'name' => 'Verspreiding',
    					'sequence' => '1'
    				)
    			),
    			(int) 2 => array(
    				'id' => '126',
    				'name' => 'Riviervallei, bossen',
    				'animal_property_id' => '3',
    				'animal_id' => '22',
    				'AnimalProperty' => array(
    					'id' => '3',
    					'name' => 'Leefgebied',
    					'sequence' => '3'
    				)
    			),
    			(int) 3 => array(
    				'id' => '127',
    				'name' => '8 à 10 jaar in het wild en 20 jaar in dierentuinen',
    				'animal_property_id' => '4',
    				'animal_id' => '22',
    				'AnimalProperty' => array(
    					'id' => '4',
    					'name' => 'Levensverwachting',
    					'sequence' => '4'
    				)
    			),
    			(int) 4 => array(
    				'id' => '128',
    				'name' => 'Mannetje 48 kilogram en vrouwtje 42,5 kilogram',
    				'animal_property_id' => '7',
    				'animal_id' => '22',
    				'AnimalProperty' => array(
    					'id' => '7',
    					'name' => 'Gewicht volwassen dier',
    					'sequence' => '6'
    				)
    			),
    			(int) 5 => array(
    				'id' => '129',
    				'name' => '500 à 700 gram',
    				'animal_property_id' => '5',
    				'animal_id' => '22',
    				'AnimalProperty' => array(
    					'id' => '5',
    					'name' => 'Gewicht pasgeboren dier',
    					'sequence' => '5'
    				)
    			),
    			(int) 6 => array(
    				'id' => '130',
    				'name' => '90 à 105 dagen',
    				'animal_property_id' => '10',
    				'animal_id' => '22',
    				'AnimalProperty' => array(
    					'id' => '10',
    					'name' => 'Draagtijd',
    					'sequence' => '10'
    				)
    			),
    			(int) 7 => array(
    				'id' => '131',
    				'name' => 'Zomer: 2,5 centimeter - winter: 7 centimeter',
    				'animal_property_id' => '15',
    				'animal_id' => '22',
    				'AnimalProperty' => array(
    					'id' => '15',
    					'name' => 'Dikte pels',
    					'sequence' => '14'
    				)
    			),
    			(int) 8 => array(
    				'id' => '132',
    				'name' => '37 kilometer per uur',
    				'animal_property_id' => '16',
    				'animal_id' => '22',
    				'AnimalProperty' => array(
    					'id' => '16',
    					'name' => 'Loopsnelheid',
    					'sequence' => '15'
    				)
    			)
    		)
    	),
    	(int) 1 => array(
    		'Animal' => array(
    			'id' => '1',
    			'name' => 'Aziatische olifant',
    			'sequence' => '2'
    		),
    		'Area' => array(
    			'name' => 'Savanne',
    			'id' => '7'
    		),
    		'AnimalPhoto' => array(
    			(int) 0 => array(
    				'name' => '1491420984-aziatische-olifant-1.jpg',
    				'alt' => null,
    				'title' => null,
    				'sequence' => '1',
    				'animal_id' => '1'
    			)
    		),
    		'AnimalPropertyLink' => array(
    			(int) 0 => array(
    				'id' => '1',
    				'name' => 'Bedreigd',
    				'animal_property_id' => '1',
    				'animal_id' => '1',
    				'AnimalProperty' => array(
    					'id' => '1',
    					'name' => 'Status',
    					'sequence' => '2'
    				)
    			),
    			(int) 1 => array(
    				'id' => '2',
    				'name' => 'Zuid-Oost Azië',
    				'animal_property_id' => '2',
    				'animal_id' => '1',
    				'AnimalProperty' => array(
    					'id' => '2',
    					'name' => 'Verspreiding',
    					'sequence' => '1'
    				)
    			),
    			(int) 2 => array(
    				'id' => '3',
    				'name' => 'Regenwoud, bossen, savanne',
    				'animal_property_id' => '3',
    				'animal_id' => '1',
    				'AnimalProperty' => array(
    					'id' => '3',
    					'name' => 'Leefgebied',
    					'sequence' => '3'
    				)
    			),
    			(int) 3 => array(
    				'id' => '4',
    				'name' => '65 à 70 jaar',
    				'animal_property_id' => '4',
    				'animal_id' => '1',
    				'AnimalProperty' => array(
    					'id' => '4',
    					'name' => 'Levensverwachting',
    					'sequence' => '4'
    				)
    			),
    			(int) 4 => array(
    				'id' => '5',
    				'name' => '120 kilogram',
    				'animal_property_id' => '5',
    				'animal_id' => '1',
    				'AnimalProperty' => array(
    					'id' => '5',
    					'name' => 'Gewicht pasgeboren dier',
    					'sequence' => '5'
    				)
    			),
    			(int) 5 => array(
    				'id' => '6',
    				'name' => '3 à 5 ton',
    				'animal_property_id' => '7',
    				'animal_id' => '1',
    				'AnimalProperty' => array(
    					'id' => '7',
    					'name' => 'Gewicht volwassen dier',
    					'sequence' => '6'
    				)
    			),
    			(int) 6 => array(
    				'id' => '7',
    				'name' => 'Op 3,5 à 4 jaar',
    				'animal_property_id' => '9',
    				'animal_id' => '1',
    				'AnimalProperty' => array(
    					'id' => '9',
    					'name' => 'Eerste zwangerschap',
    					'sequence' => '9'
    				)
    			),
    			(int) 7 => array(
    				'id' => '8',
    				'name' => '22 à 24 maand',
    				'animal_property_id' => '10',
    				'animal_id' => '1',
    				'AnimalProperty' => array(
    					'id' => '10',
    					'name' => 'Draagtijd',
    					'sequence' => '10'
    				)
    			),
    			(int) 8 => array(
    				'id' => '9',
    				'name' => 'De mens',
    				'animal_property_id' => '11',
    				'animal_id' => '1',
    				'AnimalProperty' => array(
    					'id' => '11',
    					'name' => 'Grootste vijand',
    					'sequence' => '16'
    				)
    			)
    		)
    	),
    	(int) 2 => array(
    		'Animal' => array(
    			'id' => '2',
    			'name' => 'Bengaalse tijger',
    			'sequence' => '3'
    		),
    		'Area' => array(
    			'name' => 'India',
    			'id' => '5'
    		),
    		'AnimalPhoto' => array(
    			(int) 0 => array(
    				'name' => '1491421228-bengaalse-tijger-1.jpg',
    				'alt' => null,
    				'title' => null,
    				'sequence' => '0',
    				'animal_id' => '2'
    			)
    		),
    		'AnimalPropertyLink' => array(
    			(int) 0 => array(
    				'id' => '10',
    				'name' => 'Bedreigd',
    				'animal_property_id' => '1',
    				'animal_id' => '2',
    				'AnimalProperty' => array(
    					'id' => '1',
    					'name' => 'Status',
    					'sequence' => '2'
    				)
    			),
    			(int) 1 => array(
    				'id' => '11',
    				'name' => 'Zuid-Oost Azië',
    				'animal_property_id' => '2',
    				'animal_id' => '2',
    				'AnimalProperty' => array(
    					'id' => '2',
    					'name' => 'Verspreiding',
    					'sequence' => '1'
    				)
    			),
    			(int) 2 => array(
    				'id' => '12',
    				'name' => 'Regenwouden, bossen, taiga, grasland',
    				'animal_property_id' => '3',
    				'animal_id' => '2',
    				'AnimalProperty' => array(
    					'id' => '3',
    					'name' => 'Leefgebied',
    					'sequence' => '3'
    				)
    			)
    		)
    	),
    	(int) 3 => array(
    		'Animal' => array(
    			'id' => '3',
    			'name' => 'Bennett's wallabie',
    			'sequence' => '4'
    		),
    		'Area' => array(
    			'name' => 'Kidspark',
    			'id' => '4'
    		),
    		'AnimalPhoto' => array(),
    		'AnimalPropertyLink' => array(
    			(int) 0 => array(
    				'id' => '13',
    				'name' => 'Veilig',
    				'animal_property_id' => '1',
    				'animal_id' => '3',
    				'AnimalProperty' => array(
    					'id' => '1',
    					'name' => 'Status',
    					'sequence' => '2'
    				)
    			),
    			(int) 1 => array(
    				'id' => '14',
    				'name' => 'Oost-Australië, Tasmanië',
    				'animal_property_id' => '2',
    				'animal_id' => '3',
    				'AnimalProperty' => array(
    					'id' => '2',
    					'name' => 'Verspreiding',
    					'sequence' => '1'
    				)
    			),
    			(int) 2 => array(
    				'id' => '15',
    				'name' => 'Bossen, graslanden',
    				'animal_property_id' => '3',
    				'animal_id' => '3',
    				'AnimalProperty' => array(
    					'id' => '3',
    					'name' => 'Leefgebied',
    					'sequence' => '3'
    				)
    			)
    		)
    	),
    	(int) 4 => array(
    		'Animal' => array(
    			'id' => '27',
    			'name' => 'Blauwgele ara',
    			'sequence' => '5'
    		),
    		'Area' => array(
    			'name' => 'Jungle',
    			'id' => '3'
    		),
    		'AnimalPhoto' => array(),
    		'AnimalPropertyLink' => array(
    			(int) 0 => array(
    				'id' => '111',
    				'name' => 'Veilig',
    				'animal_property_id' => '1',
    				'animal_id' => '27',
    				'AnimalProperty' => array(
    					'id' => '1',
    					'name' => 'Status',
    					'sequence' => '2'
    				)
    			),
    			(int) 1 => array(
    				'id' => '112',
    				'name' => 'Zuid-Amerika',
    				'animal_property_id' => '2',
    				'animal_id' => '27',
    				'AnimalProperty' => array(
    					'id' => '2',
    					'name' => 'Verspreiding',
    					'sequence' => '1'
    				)
    			),
    			(int) 2 => array(
    				'id' => '113',
    				'name' => 'Regenwouden en savannes in de buurt van water',
    				'animal_property_id' => '3',
    				'animal_id' => '27',
    				'AnimalProperty' => array(
    					'id' => '3',
    					'name' => 'Leefgebied',
    					'sequence' => '3'
    				)
    			),
    			(int) 3 => array(
    				'id' => '114',
    				'name' => '81 - 91,5 centimeter',
    				'animal_property_id' => '8',
    				'animal_id' => '27',
    				'AnimalProperty' => array(
    					'id' => '8',
    					'name' => 'Hoogte volwassen dier',
    					'sequence' => '8'
    				)
    			),
    			(int) 4 => array(
    				'id' => '115',
    				'name' => '900 - 1800 gram',
    				'animal_property_id' => '7',
    				'animal_id' => '27',
    				'AnimalProperty' => array(
    					'id' => '7',
    					'name' => 'Gewicht volwassen dier',
    					'sequence' => '6'
    				)
    			),
    			(int) 5 => array(
    				'id' => '116',
    				'name' => '50 jaar',
    				'animal_property_id' => '4',
    				'animal_id' => '27',
    				'AnimalProperty' => array(
    					'id' => '4',
    					'name' => 'Levensverwachting',
    					'sequence' => '4'
    				)
    			),
    			(int) 6 => array(
    				'id' => '117',
    				'name' => '3 - 4 jaar',
    				'animal_property_id' => '9',
    				'animal_id' => '27',
    				'AnimalProperty' => array(
    					'id' => '9',
    					'name' => 'Eerste zwangerschap',
    					'sequence' => '9'
    				)
    			),
    			(int) 7 => array(
    				'id' => '118',
    				'name' => '2 tot 4',
    				'animal_property_id' => '12',
    				'animal_id' => '27',
    				'AnimalProperty' => array(
    					'id' => '12',
    					'name' => 'Aantal eieren per broed',
    					'sequence' => '11'
    				)
    			),
    			(int) 8 => array(
    				'id' => '119',
    				'name' => '24 - 28 dagen',
    				'animal_property_id' => '13',
    				'animal_id' => '27',
    				'AnimalProperty' => array(
    					'id' => '13',
    					'name' => 'Broedtijd',
    					'sequence' => '12'
    				)
    			),
    			(int) 9 => array(
    				'id' => '120',
    				'name' => '104 tot 114 centimeter',
    				'animal_property_id' => '14',
    				'animal_id' => '27',
    				'AnimalProperty' => array(
    					'id' => '14',
    					'name' => 'Vleugelspan',
    					'sequence' => '13'
    				)
    			)
    		)
    	),
    	(int) 5 => array(
    		'Animal' => array(
    			'id' => '23',
    			'name' => 'Capibara',
    			'sequence' => '6'
    		),
    		'Area' => array(
    			'name' => 'Jungle',
    			'id' => '3'
    		),
    		'AnimalPhoto' => array(),
    		'AnimalPropertyLink' => array(
    			(int) 0 => array(
    				'id' => '85',
    				'name' => 'Veilig',
    				'animal_property_id' => '1',
    				'animal_id' => '23',
    				'AnimalProperty' => array(
    					'id' => '1',
    					'name' => 'Status',
    					'sequence' => '2'
    				)
    			),
    			(int) 1 => array(
    				'id' => '86',
    				'name' => 'Amazonegebied (centraal en oostelijk deel van Zuid-Amerika)',
    				'animal_property_id' => '2',
    				'animal_id' => '23',
    				'AnimalProperty' => array(
    					'id' => '2',
    					'name' => 'Verspreiding',
    					'sequence' => '1'
    				)
    			),
    			(int) 2 => array(
    				'id' => '87',
    				'name' => 'Bosgebied in nabijheid van water of moerassen',
    				'animal_property_id' => '3',
    				'animal_id' => '23',
    				'AnimalProperty' => array(
    					'id' => '3',
    					'name' => 'Leefgebied',
    					'sequence' => '3'
    				)
    			)
    		)
    	)
    )

But still I’m not able to sort the AnimalPropertyLinks by AnimalProperty sequence. That’s the attempt where I get the error 'SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘AnimalProperty.sequence’ in ‘order clause’:

    $this->loadModel('Animal');
    
    $findOptions['recursive'] = -1;
    
    $findOptions['fields'] = array(
        'Animal.id',
        'Animal.name',
        'Animal.sequence'
    );
    
    $findOptions['order'] = array(
        'Animal.sequence' => 'ASC'
    );
    
    $findOptions['contain'] = array(
        'Area' => array(
            'fields' => array(
                'Area.name'
            ),
        ),
        'AnimalPhoto' => array(
            'fields' => array(
                'AnimalPhoto.name',
                'AnimalPhoto.alt',
                'AnimalPhoto.title',
                'AnimalPhoto.sequence'
            ),
            'order' => array(
                'AnimalPhoto.sequence' => 'ASC'
            ),
            'limit' => 1
        ),
        'AnimalPropertyLink' => array(
            'fields' => array(
                'AnimalPropertyLink.id',
                'AnimalPropertyLink.name'
            ),
            'order' => array(
                'AnimalProperty.sequence' => 'DESC'
            ),
            'AnimalProperty' => array(
                'fields' => array(
                    'AnimalProperty.id',
                    'AnimalProperty.name',
                    'AnimalProperty.sequence'
                )
            )
        )
    );
    
    $animal = $this->Animal->find('all', $findOptions);

(I’ve added the ‘order’ in AnimalPropertyLinks contain statement).

Can anyone help me ordering the animal_property_links by animal_property.sequence please? I’m out of options I’m afraid
I also tried to do some manual join stuff, but then I’ve got one animal multiple times…

samclauw · January 10, 2018, 10:06am

I guess this will never be solved. The fact is that the containable behavior generates multiple ‘AnimalProperty’ queries and I am not able to reorder those generated queries:

SELECT `AnimalPropertyLink`.`name`, `AnimalPropertyLink`.`animal_property_id`, `AnimalPropertyLink`.`animal_id` FROM `bellewaerdefun`.`animal_property_links` AS `AnimalPropertyLink` WHERE `AnimalPropertyLink`.`deleted` IS NULL AND `AnimalPropertyLink`.`animal_id` = (1)
SELECT `AnimalProperty`.`name`, `AnimalProperty`.`sequence` FROM `bellewaerdefun`.`animal_properties` AS `AnimalProperty` WHERE `AnimalProperty`.`id` = 1 AND `AnimalProperty`.`deleted` IS NULL ORDER BY `AnimalProperty`.`sequence` DESC
SELECT `AnimalProperty`.`name`, `AnimalProperty`.`sequence` FROM `bellewaerdefun`.`animal_properties` AS `AnimalProperty` WHERE `AnimalProperty`.`id` = 2 AND `AnimalProperty`.`deleted` IS NULL ORDER BY `AnimalProperty`.`sequence` DESC
SELECT `AnimalProperty`.`name`, `AnimalProperty`.`sequence` FROM `bellewaerdefun`.`animal_properties` AS `AnimalProperty` WHERE `AnimalProperty`.`id` = 3 AND `AnimalProperty`.`deleted` IS NULL ORDER BY `AnimalProperty`.`sequence` DESC
...

So perhaps I should just use the containable behavior to get the associated Area and AnimalPhoto’s and use another find operation to get the AnimalPropertyLinks so I can sort it by AnimalProperty? That’s in fact the way I always did it until now…

But to be honest then: why the hell didn’t they find a solution for that?

Topic		Replies	Views
Cakephp 4 Sort belongstomany by joinData Need Help	15	2837	July 27, 2021
Sorting hasOne Relationship Need Help	1	561	September 8, 2018
Paginating and sorting associated calculated fields Need Help help	4	590	July 1, 2022
How to order rows in table class in cakephp 4.2 Need Help	3	318	July 19, 2023
Sort from view with when using hasOne associations Need Help	3	989	February 18, 2019

Order linked table in a has many through relation association (CakePhp 2)

Releases

Documentation

Community

Order linked table in a has many through relation association (CakePhp 2)

Related topics

Releases

Documentation

Community