Databases
StackOverflow2013
Postsdbo
POCakephp 2 Paginate Order By Closest Date
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCakephp 2 Paginate Order By Closest Date
    primarykey
    data
    text
    <p>I am trying to get the list of results from the Orders table in my CakePhp 2.x application to sort themselves by the date closest to today. </p> <p>In a usual mysql query I have had something similar working with say the following syntax:</p> <pre><code>ABS(DATEDIFF(Order.duedate, NOW())) </code></pre> <p>However in Cake I am not sure how to get such a custom query to work within the paginate helper. Is this something I may need to set in a finder query in the model?</p> <p>Here is the controller I currently have (set to a bog standard descending sort)</p> <pre><code> public function index() { $this-&gt;paginate = array( 'conditions' =&gt; array ( 'Order.despatched' =&gt; array('Not Despatched' , 'Split Despatched') ), 'limit' =&gt; 25, 'order' =&gt; array('Order.duedate' =&gt; 'DESC') ); $data = $this-&gt;paginate('Order'); $this-&gt;set('orders', $data); } </code></pre> <p>Edit: Using information from the comment below I added a virtual field into the controller which causes an sql error. At first I thought this was due to the associations within the model, to attempt to rectify this I added the virtualField into the Order Model and into the constructor of the associated Account Model. However this made no change to the part of the sql which breaks. The full sql error is:</p> <pre><code>Error: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS `Order__closestdate`, `Account`.`id`, `Account`.`company`, `Account`.`contac' at line 1 SQL Query: SELECT `Order`.`id`, `Order`.`order_id`, `Order`.`orderdate`, `Order`.`duedate`, `Order`.`rework`, `Order`.`rework_notes`, `Order`.`comments`, `Order`.`status`, `Order`.`customer`, `Order`.`last_edited`, `Order`.`value`, `Order`.`quantity`, `Order`.`order_for`, `Order`.`warehouse`, `Order`.`haulier`, `Order`.`terms`, `Order`.`type`, `Order`.`despatched`, `Order`.`despatched_date`, `Order`.`invoiced`, `Order`.`invoice_date`, `Order`.`bookingref`, `Order`.`purchaseref`, `Order`.`payment_due`, `Order`.`payment_status`, `Order`.`payment_date`, (ABS(DATEDIFF(duedate, NOW())) AS `Order__closestdate`, `Account`.`id`, `Account`.`company`, `Account`.`contact`, `Account`.`phone`, `Account`.`email`, `Account`.`postcode`, `Account`.`created`, `Account`.`modified`, `Account`.`customer`, `Account`.`address1`, `Account`.`address2`, `Account`.`town`, `Account`.`county`, (ABS(DATEDIFF(duedate, NOW())) AS `Account__closestdate` FROM `hunter_om`.`orders` AS `Order` LEFT JOIN `hunter_om`.`accounts` AS `Account` ON (`Order`.`customer` = `Account`.`customer`) WHERE `Order`.`despatched` IN ('Not Despatched', 'Split Despatched') ORDER BY (ABS(DATEDIFF(duedate, NOW())) DESC LIMIT 25 </code></pre> <p>For reference the code in the models are:</p> <pre><code>//Order Model public $virtualFields = array( 'closestdate' =&gt; 'ABS(DATEDIFF(duedate, NOW())' ); //Account Model public function __construct($id = false, $table = null, $ds = null) { parent::__construct($id, $table, $ds); $this-&gt;virtualFields['closestdate'] = $this-&gt;Order-&gt;virtualFields['closestdate']; } </code></pre> <p>Any help is appreciated thanks.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USDevelopingTheWeb
    1. USDevelopingTheWeb
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload