2

I have table orders which has column files. The column holds file id's and they are comma separated.

Second table is documents.

Table Name : orders

 orders_id | order_details | file_id
 ------------------------------------
     1     | some details  |   1,2

Table Name : documents

 id |   name 
 ------------------
  1 | file name
  2 | file2 name2

Currently I have this query which query only orders table but I want to join documents also so I can show to customer the names of documents which are connected in file_id column in orders

$docomuntOrders = Order::where('user_id',getCurrentUser()->user_id)
                        ->orderBy('order_id', 'DESC')
                        ->paginate(10);

Can you guide a little bit here?

Improve this question
3
  • 1
    You should use order_id in the documents table to define relationship If it's is in development phase. Other wise json can be another good option Commented Jul 18, 2017 at 10:51
  • But documents is separated table. When I upload documents via admin panel is stored there. I don't know order_id and I should not need to know it. I'm not sure I understand you. Commented Jul 18, 2017 at 10:53
  • You can use array_map to get file_id of one column but with all it will be difficult to get. Commented Jul 18, 2017 at 11:08

2 Answers 2

Reset to default
12

Hope It will work for you. have a try

$docomuntOrders = \DB::table("orders")
            ->select("orders.*",\DB::raw("GROUP_CONCAT(documents.name) as docname"))
            ->leftjoin("documents",\DB::raw("FIND_IN_SET(documents.id,orders.file_id)"),">",\DB::raw("'0'"))
            ->where('user_id',getCurrentUser()->user_id)
            ->groupBy("orders.id")
            ->paginate(10);

if you try it dd($docomuntOrders) hope it will return desired result.

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Quick question: How to select then document_name for example? when I do @foreach($downloadableOrders as $download) { $download->document_name } i got NULL
@user5996816 i update my answer and i think its ready for you :)
int(87) which is correct id for the user I'm currently logged in
Also I can see it in dd($docomuntOrders) but when I remove dd($docomuntOrders) and tried to load the page got the error above
Let us continue this discussion in chat.
|
0

A single user has multiple company and company id's are in users table in a single filed with comma separation. In that case this is the solution, which is very similar to your problem.

Users::leftJoin("company_info as ci",\DB::raw("FIND_IN_SET(ci.id,users.company_ids)"),">",\DB::raw("'0'"))
    ->groupBy('users.id')
    ->get([
        'users.*',
        \DB::raw("GROUP_CONCAT(ci.company_name) as company_name")
    ]);
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.