How do I create a custom attribute in a schema using the SUM function, or should I use a model accessor instead?

[ibrahimalanshor]

How do I create a custom attribute in a schema that results from the SUM function? For instance, there's a products table with a has-many relationship to product_prices and a many-to-many relationship to materials through material_products. I want to display all products with a column price that results from summing all price columns in product_prices and the product of the qty column in material_products with the price column in materials' Here's an example SQL code:

SELECT
    p.product_id,
    p.product_name,
    SUM(pp.price) AS product_price,
    SUM(m.price * pm.qty) AS material_price,
    SUM(pp.price) + SUM(m.price * pm.qty) AS final_price
FROM
    products p
LEFT JOIN
    product_prices pp ON p.product_id = pp.product_id
LEFT JOIN
    material_products pm ON p.product_id = pm.product_id
LEFT JOIN
    materials m ON pm.material_id = m.material_id
GROUP BY
    p.product_id, p.product_name

This SQL query calculates and displays the final_price attribute as the sum of product_price and material_price for each product.
Alternatively, should I use a model accessor for this purpose?

[lindyhopchris]

At the moment, you'd need to add an accessor to your model and get it like that. (The advantage to that approach though is you can then use it in other places in your code, i.e. not just in JSON:API.)

Note that it won't be efficient though if you're retrieving zero-to-many of the resource, because you'll get an n+1 problem with the query.

[darkphoenixff4]

@lindyhopchris How would that work with a filter, though? Pretty sure you can't access filters in an accessor.

[lindyhopchris]

Yeah it wouldn't work with a filter. If we wanted to get it working in a filter, someone would need to share what the query is on the Eloquent model (i.e. how you do the query on the model outside of Laravel JSON:API) and then that should be transferrable to a Laravel JSON:API filter.