Laravel Eloquent how to get data from 2 tables within a date range

Question

I have 2 tables (example) which have something like this... In the Invoice table, I have 5 records and in the Quotation table, I have 3.

Invoice
id
price
inv_desc
invoice_date
balance

Quotation
id
qty
quo_desc
quo_date

I want to get all records (*) from both tables between date 1/1/2021 and 28/2/2021, and save it in a single collection, in order to loop and display in list. Since the number of columns and name of the date column of the 2 tables are different, how do I retrieve the records sort by ascending dates? The expected output (example) should be:

No.	Description	Quantity	Price	Balance	Date
1.	Quotation	5			1/1/2021
2.	Invoice		154.90	154.90	12/1/2021
3.	Quotation	10			23/1/2021
4.	Invoice		456.00	126.59	5/2/2021
5.	Invoice		126.59	56.70	23/2/2021

Answer 1

This is an answer in another post that I asked, similar to this question: https://stackoverflow.com/a/66454203/15102985

Answer 2

You may try a union query:

$first = DB::table('Invoice')
    ->select('description', NULL, 'price', 'balance', 'invoice_date')
    ->where('invoice_date', '>=', '2021-01-01')
    ->where('invoice_date', '<', '2021-03-01');

$result = DB::table('Quotation')
    ->select('description', 'qty', NULL, NULL, 'quo_date')
    ->where('quo_date', '>=', '2021-01-01')
    ->where('quo_date', '<', '2021-03-01');
    ->unionAll($first)
    ->get();

This corresponds to the following raw MySQL query:

SELECT description, NULL, price, balace, invoice_date
FROM Invoice
WHERE invoice_date >= '2021-01-01' AND invoice_date < '2021-03-01'
UNION ALL
SELECT description, qty, NULL, NULL, quo_date
FROM Quotation
WHERE quo_date >= '2021-01-01' AND quo_date < '2021-03-01';

Answer 3

Since eloquent, what's the relationship between Quote and Invoice? Would it be something like 1 quote will have many invoice? And I guess a Quote comes before an invoice. if it is.

1. You will need to have to add another column in Invoice. so Quotation_id
2. Create your Quotation Model

class Quotation extends Model

{
    use HasFactory;

    protected $fillable = [
        'qty',
        'description',
        'quo_date'
    ];

    public function Invoice()
    {
        return $this->hasMany(Invoice::class);
    } }

3.Create your Invoice Model

    class Invoice extends Model
    {
    use HasFactory;

    protected $fillable = [
        'price', 'description', 'invoice_date', 'subtotal' 
    ];

    public function Quotation() 
    {
         return $this->belongsTo(Quotation::class);
    }
}

4. Next in your Controller

    use App\Models\Invoice;
    use App\Models\Quoatation;
    
    $query = Quoatation::wherebetween('quo_date', [2021-01-01,2021-03-01])
    ->orderby('quo_date', 'ASC')->with('Invoice')
    ->wherebetween('invoice_date', [2021-01-01,2021-03-01])
    ->orderby('invoice_date', 'ASC')
    ->get();