Level 75
Have you taken any sql tutorials.
Some will disagree, but I suggest you learn and work examples.
https://laravel.com/docs/6.0/eloquent-relationships
Laravel has documetation.
And see http://www.mysqltutorial.org.
Sep 5, 2019
3
Level 1
Form a query with custom column with subquery as it's value
Hello, i have query that joins few tables. Result of query returns me the following column values
"prod_id" : product.id
"prod_name" : product.name
"prod_user_id" : product_user.id
"prod_user_prod_id" : product_user.product_id
"prod_user_user_id" : product_user.user_id
...
now i wanna do a second query (maybe there are other options) and join it as a value of a custom column expected result:
"prod_id" : product.id
"prod_name" : product.name
"prod_user_id" : product_user.id
"prod_user_prod_id" : product_user.product_id
"prod_user_user_id" : product_user.user_id
...
"requierements" : [
{ "id" : product_requirements.id,
"name" : product_requirements.name
..
},
{ ... }
]
How can I create custom column and put a subquery as it's value? I would be appreciated if the answer will include 3 possible options:
- pure SQL
- DB:: facade
- Eloquent
Level 1
let's say it differently.
i have a single row as a result of query:
query_result1
and second query also returns a single row
query_result2
now i wanna do some magic without PHP been involved, so the SQL query will return me something like
query_result1,
"requirements" : query_result2
i'd say "requirements" can be called a pseudo-column that actually never existed in DB, and it's value is set of key:value of second query
is it possible to do with SQL without PHP been involved? If yes, is it possible to do so with Eloquent query builder?
Level 75
Not your data here but of course you can join in eloquent, just example:
$quy = Powner::query()->leftJoin('dc_pets', 'dc_powners.ownerid', '=', 'dc_pets.ownerid')
->select('dc_powners.ownerid', 'dc_powners.oname')
->selectRaw('count(dc_pets.petid) as countOfPets')
->groupby('dc_powners.ownerid')
->orderby('dc_powners.oname')
->get();
Results basically give:
ownerid, oname, countOfPets
Like:
5|Bob|3
4|Greg|9
2|Rob|1
countOfPets is a derived column.
https://laravel.com/docs/5.8/queries
But you could also set up eloquent relations if you want to:
https://laravel.com/docs/5.8/eloquent
https://laravel.com/docs/5.8/eloquent-relationships
I'd suggest viewing some videos on some of these various topics.
And or actually work the examples Taylor provides in the documentation.
Also even if eloquent can't handle a certain complex query, you can also write normal queries in laravel.
And you can query a query:
https://laracasts.com/discuss/channels/eloquent/calculate-average-of-multiple-fields-eloquent
1 like
Please or to participate in this conversation.