well count
returns the number of total rows, not the the rows per se
arcollector said:
well
count
returns the number of total rows, not the the rows per se
It's returning 0 though. This query will eventually be used to delete the records from the queue that already exist in the listings table.
I tried:
$count = DB::table('listings_queue')
->join('listings', 'listings_queue.mls_listing_id', '=', 'listings.mls_listing_id')
->where('listings.mls_id','=','listings_queue.mls_id')
->where('listings.city' , '=', 'listings_queue.city')
->toSql();
$this->info($count);
That produces the following SQL:
select * from `listings_queue` inner join `listings` on `listings_queue`.`mls_listing_id` = `listings`.`mls_listing_id` where `listings`.`mls_id` = ? and `listings`.`city` = ?
In your original example, you have the where clauses nested in the in the join function, I think that may put the conditions on the join and not the overall statement.
$count = DB::table('listings_queue')
->join('listings', function($join)
{
$join->on('listings_queue.mls_listing_id', '=', 'listings.mls_listing_id')
->where('listings.mls_id','=','listings_queue.mls_id')
->where('listings.city' , '=', 'listings_queue.city');
})
->count();
I think what that is saying is
LEFT JOIN listings ON `listings_queue`.`mls_listing_id` = `listings`.`mls_listing_id` and `listings.mls_id` = `listings_queue.mls_id` and `listings.city` = `listings_queue.city`
and what you want is
LEFT JOIN listings ON `listings_queue`.`mls_listing_id` = `listings`.`mls_listing_id`
WHERE
`listings.mls_id` = `listings_queue.mls_id`
AND
`listings.city` = `listings_queue.city`
Try it with the where clause outside the join function and see if it works:
$count = DB::table('listings_queue')
->join('listings', function($join)
{
$join->on('listings_queue.mls_listing_id', '=', 'listings.mls_listing_id')
})
->where('listings.mls_id','=','listings_queue.mls_id')
->where('listings.city' , '=', 'listings_queue.city');
->count();
Thanks but that didn't work out either. It produced:
select * from `listings_queue`
inner join `listings`
on `listings_queue`.`mls_listing_id` = `listings`.`mls_listing_id`
where `listings`.`mls_id` = ?
and `listings`.`city` = ?
Stupid question, but are those ? causing my issue in that it's not comparing the two tables but instead looking for a value that isn't htere?
those ?
are placeholders
, they are use for security reasons as well for better code reading, check this: http://en.wikipedia.org/wiki/Prepared_statement
if you call DB::getQueryLog()
, for each query you will see the values that are binded for each placeholder
I went back to my Sequel Pro and wrote the query in just raw SQL and it works great. So, asked another way, how would I write this query using query builder?
select count(listings_queue.mls_listing_id) from listings_queue
INNER JOIN listings
ON `listings_queue`.`mls_listing_id` = `listings`.`mls_listing_id`
WHERE `listings`.`mls_id`=`listings_queue`.`mls_id`
AND `listings`.`city`=`listings_queue`.`city`
Sign in to participate in this thread!
The Laravel portal for problem solving, knowledge sharing and community building.
The community