Databases
StackOverflow2013
Postsdbo
POmySql advice needed for query in cakephp
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POmySql advice needed for query in cakephp
    primarykey
    data
    text
    <p>I have two models, listings and bookings. listings hasmany bookings. I'm trying to write a query to return all listings who DON'T have bookings booked during a certain time period.</p> <p>I've gotten so far but I'm still a newbie to cakephp and my sql isn't great either. In my controller method I have</p> <pre><code>$conditions = array( "OR" =&gt; array( "Listing.address_one LIKE" =&gt; "%".$area."%", "Listing.address_two LIKE" =&gt; "%".$area."%", "Listing.city LIKE" =&gt; "%".$area."%", "Listing.postcode LIKE" =&gt; "%".$area."%", "Listing.title LIKE" =&gt; "%".$area."%", ) ); $this-&gt;paginate = array( 'conditions'=&gt; $conditions, 'joins'=&gt;array( array( 'table'=&gt;'bookings', 'alias'=&gt;'Booking', 'type' =&gt;'inner', 'conditions' =&gt;array('Booking.listing_id = Listing.id', 'Booking.checkin NOT BETWEEN ? AND ?' =&gt; array($to, $from)) ) ), 'limit' =&gt; 10, ); $data = $this-&gt;paginate(); </code></pre> <p>That just gets all the listings which actually have bookings associated with them. Not only that but it doesn't ignore any bookings which are booked for the given period.</p> <p>The SQL it creates is:</p> <pre><code>SELECT ... FROM `database`.`listings` AS `Listing` INNER JOIN `database`.`bookings` AS `Booking` ON ( `Booking`.`listing_id` = `Listing`.`id` AND `Booking`.`checkin` NOT BETWEEN '18-07-2013' AND '10-07-2013' ) LEFT JOIN `database`.`users` AS `User` ON ( `Listing`.`user_id` = `User`.`id` ) WHERE ( (`Listing`.`address_one` LIKE '%belfast%') OR (`Listing`.`address_two` LIKE '%belfast%') OR (`Listing`.`city` LIKE '%belfast%') OR (`Listing`.`postcode` LIKE '%belfast%') OR (`Listing`.`title` LIKE '%belfast%') ) LIMIT 10 </code></pre> <p>Which looks kinda rightish(?) but obviously isn't. Anyone any ideas? Thanks </p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USZaphod Beeblebrox
    1. USZaphod Beeblebrox
    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. VO
      singulars
      1. POmySql advice needed for query in cakephp
      1. This table or related slice is empty.
      1. VTUpMod
    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