vendredi 27 octobre 2023

What is the optimised way to fetch all items and related name, email from parent table using mysql query?

Suppose we have a table of order consisting of order IDs, what would be considered 'best practice' in terms of fetching all item data with their order?

Order table:
--------

| id | name | email |  phone | event_id |
|---- |------| -----|-----| -----|
| 1    | Anish  | anish@gmail.com | 8233472332| 1765 |
| 2    | ABC    | abc@gmail.com   | 784472332| 1765 |
| 3    | XYZ    | xyz@gmail.com   | 646322332| 1525 |

items table:
------------


| id | order_id | category |  qty | price |
|---- |------| -----|-----| -----|
| 1    | 1  | person 1 | 2| 299 |
| 2    | 1    | person 2   | 1| 399 |
| 3    | 2    | person 1   | 1| 299 |

I want to fetch all items and their order name, email, phone in a single query.

Expected result

| name | email | category |  qty | price |
|---- |------| -----------|----- | ----- |
|  Anish   | anish@gmail.com   | person 1 | 2| 299 |
| Anish    | anish@gmail.com.  | person 2   | 1| 399 |
| ABC      | abc@gmail.com     | person 1   | 1| 299 |

One to many relations Orders -> items (It has multiple rows related to a single order)

I want to display all items for an event (example: event id - 1765) along with name, email, and phone. Please suggest to me the optimized mysql query for the same. I am using Laravel 5.2.



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire