Query relationship table with column that its data is an parsed array(1,2,3,4,5,6)

I have a string column in the table that have this kind of data // 1,2,3,4,5,6 comma separated data, supposedly this id has equivalent value to the another table.

I have the solution that I can get the data of the mentioned ids but its a bit mess. This is the step that I have solution

Get all data first Loop all the data Convert the column that has comma separated data to array Loop the converted column Query to another table And save the query data Is there an easy way to achieve this via eloquent query?

Thanks

Answer

I don’t know what are the situation but you can convert the table column into a n * m relationship. I mean that if you have an “array” that it’s identifying another table with multiple values it possible that the best BD structure was a new pivot table to relate the two already existing.

• For new pivot table in a similar problem see: https://stackoverflow.com/a/58706027/7702532
• For subquerying a new table in a similar problem see: https://stackoverflow.com/a/58706063/7702532

If you can’t create a new table and the subquery option is not available for your version you can use accessor & mutators for the model attribute to mutate it and do not do a query on a loop of all results from the first query. https://laravel.com/docs/8.x/eloquent-mutators#accessors-and-mutators

I’m thinking on two options for you, I have not all the data of the problem but I’m trying to help ;D :

1. In the mutator you can call the other table and return the correct value but its very expensive for your server because all item of the first query you are doing another new query.

2. Another option are return the well formed array in the mutator. After getting the first query values, merge all the arrays of your attribute and make a unique query to your second table. This option need a loop to the results of the first table to assign the correct values filtering the second query results.

The first option is more easy to code but it’s more expensive on memory and computation. The second one can be a little bit hard to code but is a good solution if you can’t change the database structure or do correctly the subqueries.

In other hand I think that you can do a custom relationship or find a community contribution.