nickdavies07
1 week ago
116
4
General

Relationship between two models via composite key?

Posted 1 week ago by nickdavies07

I'm working on an application using Laravel back-end to build up an application from an existing database of purchase orders, receipts and invoices.

The table layout is as such:

tblOrders ord_no

tblOrderLines ord_no, ord_line_no

tblReceipts rec_no

tblReceiptLines rec_no, rec_line_no, ord_no, ord_line_no

tblInvoices inv_no

tblInvoiceLines inv_no, inv_line_no, rec_no, rec_line_no

I have models for each of those tables; Order, OrderLine, Receipt, ReceiptLine, Invoice, InvoiceLine.

What I'm trying to do is retrieve the receipt and invoice numbers of an order line (should they exist) however I'm struggling to see how I can get the relationship between e.g. OrderLine and ReceiptLine models using relationships due to the composite key of the order number and the order line number.

Is there a clear way of doing this that I'm overlooking?

Is my best answer to use the database query builder and join the two tables instead?

OrderLine

class OrderLine extends Model
{
    protected $table = 'dbo.tblOrderLines';

    public function order()
    {
        return $this->belongsTo(Order::class, 'ord_no', 'ord_no');
    }
}

ReceiptLine

class ReceiptLine extends Model
{
    protected $table = 'dbo.tblReceiptLines';

    public function receipt()
    {
        return $this->belongsTo(Receipt::class, 'rec_no', 'rec_no');
    }
}

Please sign in or create an account to participate in this conversation.