How to alias polymorphic table in Laravel/Eloquent

Question

There is following models and table

CallRequest (parent)
    'id',
    'parent_id',
    'phone_number',
    'extension_id',
    'extension_type',

public $morphTo = [
    'extension' => [],
];


AsapLead (children)
    'id'

public $morphOne = [
    'call_request' => [
        CallRequest::class,
        'name' => 'extension',
    ],
];

with polymorphic relation in it. In order to avoid pivot table, all of the data is stored in one table, thus parent Calls won't have parent_id, extension_id and extension_type filled. Only children will have those. Asap lead have only id, rest of the needed information is in it's parent.

Flow: Firstly, it's created parent Call with parent_id = null. If call fails, then it's created child call connected with the previous one by the parent_id. Additionaly it's added extension_type, because the are more than one extensions, but not to complicate much, we operate in this situation on only one. Then I need to retrieve that parent calls, which have maximum 3 children and are created not before 7 days back. Query look like this:

$callRequestTable = CallRequest::table();
$leadTable = CallRequest::table() . " as lead";

DB::table($leadTable)
    ->rightjoin($callRequestTable, 'lead.id', '=', $callRequestTable . '.parent_id')
    ->where($callRequestTable . '.extension_type', '=', AsapLead::class)
    ->where($callRequestTable . '.created_at', '>', Carbon::now()->subDays(7))
    ->groupBy('lead.id')
    ->having(DB::raw('count(*)'), '<', 3)
    ->select('lead.*')
    ->get();

But unfortunately it does not work. It would be even better to operate on CallRequest::... in order to get at the end those models instead of plain array, but also I haven't been able to figure it out.

Answer 1

This finally worked for me:

$callRequestTable = CallRequest::table();
$leadTable = CallRequest::table() . " as lead";

return CallRequest::from(DB::raw($leadTable))
    ->leftJoin($callRequestTable, 'lead.id', '=', $callRequestTable . '.parent_id')
    ->where('lead.extension_type', '=', AsapLead::class)
    ->where('lead.created_at', '>', Carbon::now()->subDays(7))
    ->groupBy('lead.id')
    ->having(DB::raw('count(*)'), '<', 3)
    ->select('lead.*');