magi010731's avatar

Duplicate Records with pagination

Greetings,

I hope someone can help me. I am having an issue with duplicate records being included in my search while doing several joins and using pagination. Here is my query:

$query = Property::query();
$query->join('calcs', 'properties.id', '=', 'calcs.property_id')->whereHas('calcs');
$query->leftJoin('transactions', 'properties.id', '=', 'transactions.property_id');
$query->leftJoin('offers', 'properties.id', '=', 'offers.property_id');                
$query->select('properties.*', 'calcs.*', 'transactions.sale_date', 'offers.status', 'offers.id');
$query->distinct();
$query->orderByRaw('calcs.flip_score DESC NULLS LAST');
$query->paginate($paginate);

I also have this on my property object:

return DB::SELECT('SELECT ST_X(location::geometry) as longitude,ST_Y(location::geometry) as latitude FROM properties WHERE id = ?', [$this->id]);
/**
  * This method dynamically adds latitude and longitude to App\Property object
  */
public function newQuery()
{
  return parent::newQuery()->select('properties.*', \DB::raw('ST_X(location::geometry) as longitude,ST_Y(location::geometry) as latitude'));
}
0 likes
3 replies
Tray2's avatar

What does the SQL look like?

$query->toSql();
magi010731's avatar
select distinct "properties".*, "calcs".*, "transactions"."sale_date", "offers"."status", "offers"."id" from "properties" inner join "calcs" on "properties"."id" = "calcs"."property_id" left join "transactions" on "properties"."id" = "transactions"."property_id" left join "offers" on "properties"."id" = "offers"."property_id" where exists (select * from "calcs" where "properties"."id" = "calcs"."property_id") order by calcs.flip_score DESC NULLS LAST
magi010731's avatar

I figured it out; I have a one to many relationship between transactions and properties...that was causing duplicate records if we have more than one transaction in the system.

Please or to participate in this conversation.