Eloquent — Merge Data from Three or More Tables — Different Structures
How to retrieve data from three or more unrelated/related tables with different table structures of a database in 07 lines of code?
This is a very rare case in the field of database engineering because most developers consider to follow standards of database structural patterns and ethics that improves the redundancy of such situations. If you’ve encountered this issue, then its indefinite that your database structure doesn’t follow a proper table relationship; meaning you got to go back to the sketch board for Entity Relationship Diagrams.
If you feel like redoing is not an option then feel free to stick around and you will find your answer below.
IS IT POSSIBLE TO FIX THIS USING CODE?
YES! There is a work around to this issue and I know your here reading this blog because you’re either wasted spilling your brains on this matter or have googled every keyword the Google S.E.O. could ever match this case scenario.
IS IT HARD TO DO THIS? DOES IT TAKE TIME?
Now to the point — In order to solve this issue of retrieving data from three or more data tables (related or unrelated) with different table structures, into one table (or variable), you need to have a clear understanding of how the RAW QUERY commands work because, there are two ways in resolving this issue, the second of which uses the system of Laravel helpers; a derived solution to the issues of RAW Queries.
01 — About RAW Query & How They Affect?
Raw query — as the name states are raw SQL commands used to retrieve any data from a database, but in this case a raw SQL retrieval has a lot of limitations hence why Eloquent became the solution for developers in the first place.
Issues you will face when writing a raw query,
- When using raw query, we need to specify the respective column names that are needed to retrieve from the tables hence, the limitation here is all the three table structure need to follow the same table structures in terms of attributes.
A work around for the above situation can be done by renaming the respective columns or attributes when reading them through the SELECT statement.
- For the case of retrieving cascaded data from the raw query, this will be a frustrating show.
What is cascaded data?
Cascaded data are data that have a relationship with another group of data. For example: in a STUDENT-COURSE relationship, data of a STUDENT information contains cascaded data of a COURSE data. If we delete a COURSE data, all relationships between that data and students will be deleted.
- In the case of retrieving cascaded data from one table, the whole query will be effected due to the rule of “equal attribute selections”. Hence RAW query is not an acceptable means to read multiple relations.
02 — About Merge function & How to use it?
Now this term maybe new to you, but it has been lying around for a couple of years.
What is the advantage of using Merge?
Well, the merge helper function provides us the ability to use Eloquent ORM classes to play with the data contained within it. This is very useful when u need to read data within the view files or blade template. Running a query within the front-end file is not an acceptable risk.
This is exactly a situation where you’d use this function and hence why it’s quite rare. The Merge terminology actually merges two Laravel collections together so you can still access the arrays including nested arrays.
What is a Laravel Collection?
As you can see, theCollectionclass allows you to chain its methods to perform fluent mapping and reducing of the underlying array. In general, collections are immutable, meaning everyCollectionmethod returns an entirely newCollectioninstance.
That’s what you get for reading the Laravel official documentation. In simple terms, the most user-friendly result that is returned from an Eloquent query is a collection. The Merge not only works for query collections it works for any array; those of which are converted into a Collection.
How does a collection look like?
Yes, it definitely looks like that, now that you realized you have used it before, you can come to a conclusion that playing with collections isn’t that hard. Note: the variable “fetch” is a laravel collection — a type of array that stores the DB records and able to run Eloquent classes to perform DB operations.
So moving on, how do we use a Laravel helper “Collection” and “Merge” to get three data tables with different table structures into one table or variable? Here goes nothing..
We will follow the standard structure of reading data from the database using Eloquent ORM of all three tables into three variables. Then we will create a new line of code to merge the three tables in just a ew lines of code; see code attached code gist.
Confusions? okay so, this is how it works. Laravel will merge the three tables into 01 collection, that will be serialized based on the order of the table names that have been merged. It will not require a similar table structure since this is a collection and not a Raw SQL query. You will notice i have appended an extra piece of code to perform a sort function. This part will run if the attribute name updated_by is available on all of the tables merged or values within the collection.
So, how do we display the data within this collection?
It’s simple as you have done it before for other individual collections. You will need to pass the variable collection into the view file and then you can run a foreach loop around the variable within the blade template, using blade’s @foreach helper class.
Voila! That is all you need to know about the merge helper function.
I have not explained with additional data to make this bog more brief & concise for you to get the right knowledge.
If you have any questions regarding the process, comment below or reach me on my Stack Overflow account. Note that this method is functional on Laravel 8 & below. There may be small changes needed to be made for other versions due to the complexity or deprecated libraries of Laravel helpers.
References
- Stack Overflow Forum: How to make composite primary keys in Laravel
- Laravel Installation: Installation — Laravel — The PHP Framework For Web Artisans
- Laravel Discussion Forum: Laracast.com
- Stay up to Date with: Laravel News
