Hello, I'm quite new to October and lost in the huge documentation... Could you please give me advices on database best practices ?

1) Should I refer to Laravel or October documentations ?

2) What is the best way for complex queries with joined tables ? Is Query Builder the best way ? I would expect "regular" SQL queries and I am disturbed by the Query Builder syntax...

3) are there queries differences between Backend plugins and Frontend partials ?

Thanks for helping !

Incremental said:

Hello, I'm quite new to October and lost in the huge documentation... Could you please give me advices on database best practices ?

1) Should I refer to Laravel or October documentations ?

Both are fine, although I suggest reading Laravel docs when you don't find what you need in october docs.

2) What is the best way for complex queries with joined tables ? Is Query Builder the best way ? I would expect "regular" SQL queries and I am disturbed by the Query Builder syntax...

It's definitely a challenge at first, but the query builder is actually pretty good. The really nice thing though is that you always have the chance to use raw queries whenever you want. The query builder has many methods that allow you to specify a raw query (or part of a query). Some examples include :

selectRaw
whereRaw
havingRaw
orderByRaw

3) are there queries differences between Backend plugins and Frontend partials ?

Can you elaborate on this, I don't get the reference to queries in partials.

October also adds Db::select/insert/update/delete() methods for raw queries:

https://octobercms.com/docs/database/basics#running-queries

Thanks for the raw trick.

I now have to deal with a multi-table query and will probably finish on www.stackoverflow.com for the moment

My query should be :

SELECT emp_name, GROUP_CONCAT(dept_name)
FROM Employee e
LEFT JOIN Work w ON (e.emp_id=w.emp_id)
LEFT JOIN Department d ON (w.dept_id=d.dept_id)
GROUP BY emp_name;

How could I perform a GROUP_CONCAT() ??? Thanks

Incremental said: How could I perform a GROUP_CONCAT() ???

Check selectRaw: https://laravel.com/docs/5.5/queries#raw-expressions

Thanks all, my query is working perfectly like :

$this["TimeSlot"] = Db::table('lb_events_events')
 ->leftjoin('lb_events_categs', 'lb_events_events.categ', '=', 'lb_events_categs.id')
 ->leftjoin('lb_events_events_types', 'lb_events_events.id', '=', 'lb_events_events_types.event_id')
 ->leftjoin('lb_events_types', 'lb_events_events_types.type_id', '=', 'lb_events_types.id')
 ->select('lb_events_events.*', 'lb_events_categs.categ_nom', DB::raw('GROUP_CONCAT(lb_events_types.type_nom) AS type_nom'))
 ->groupBy('lb_events_events.nom')
 ->orderBy('date_debut', 'ASC')
 ->get();