Support the ongoing development of Laravel.io →
posted 10 years ago
Database
Last updated 2 years ago.
0

well count returns the number of total rows, not the the rows per se

Last updated 2 years ago.
0

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` = ?
Last updated 2 years ago.
0

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();
Last updated 2 years ago.
0

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?

Last updated 2 years ago.
0

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

Last updated 2 years ago.
0

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`
Last updated 2 years ago.
0

i have same problem

0

Sign in to participate in this thread!

Eventy

Your banner here too?

ChrisF79 chrisf79 Joined 11 Feb 2014

Moderators

We'd like to thank these amazing companies for supporting us

Your logo here?

Laravel.io

The Laravel portal for problem solving, knowledge sharing and community building.

© 2024 Laravel.io - All rights reserved.