Laravel DB query gives a set of results different from direct MySQL query

Question

My DB is:

|category_id|category_slug |category_name |parent_category_id|
|1          |Main Category1|Main Category1|0                 |
|2          |Main Category2|Main Category2|0                 | 
|3          |Sub Category1 |Sub Category1 |1                 |
|4          |Sub Category2 |Sub Category2 |1                 |
|5          |Sub Category3 |Sub Category3 |2                 |

I am trying to execute the following query in MySQL

SELECT *
FROM categories AS subcategory 
JOIN categories AS parent ON parent.category_id = subcategory.parent_category_id 
WHERE subcategory.category_display_type = 'sidebar'
  AND subcategory.category_visibility = 1

like so:

class SidebarnavComposer
{
    public function compose(View $view)
    {
        $params = DB::table('categories as subs')->join('categories as cats', function($join){
                $join->on('subs.parent_category_id', '=', 'cats.category_id')
                ->where('subs.category_display_type', '=', 'sidebar')
                ->where('subs.category_visibility', '=', 1);
    })->get();


        $view->with('subcategories', $params);

    }

the problem is that I get different set of results.

When I use the first query directly in MySQL I get the following:

category_id
category_id1
category_title
category_title1
category_slug
category_slug1
.......

when I execute the second query in my laravel application I get the following set of results:

category_id
category_title
category_slug
.......

this basically means that I cannot access all necessary columns.

So what I need to be able to do is to build URI for my subcategories in the following way:

category_slug1/category_slug

where category_slug1 is main category slug and category_slug is subdirectory slug. The problem is that I only get the main category slug and cannot get slug for my subcategory in URI.

I am not sure what I am doing wrong as I am new to laravel and at the moment do not really know much, so please help me if you can

Naincy · Accepted Answer · 2017-03-01 11:20:12Z

1

Use this:

$data = DB::table('categories AS subcategory')
        ->join('categories AS parent', 'parent.category_id', '=', 'subcategory.parent_category_id')
        ->where('subcategory.category_display_type', '=', 'sidebar')
        ->where('subcategory.category_visibility', '=', 1)
        ->select('subcategory.category_slug AS category_slug1', 'parent.category_slug AS category_slug')
        ->get();

edited Mar 1, 2017 at 11:20

answered Mar 1, 2017 at 10:27

Naincy

2,9431 gold badge15 silver badges21 bronze badges

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

6 Comments

AlexB Over a year ago

Hi Naincy, i get the following error: 'code'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'subcategory.parent_category_id ' in 'on clause' (SQL: select subcategory.*, parent.* from categories as subcategory inner join categories as parent on parent.category_id = subcategory.parent_category_id where subcategory.category_display_type = sidebar and subcategory.category_visibility = 1)

Naincy Over a year ago

@AlexB You can change it as per your DB ... that is something I have taken from your question only... please see the exact field name from your DB

AlexB Over a year ago

I fixed the typo, but the results that I get is the same so I only get the results for main categories and what I actually need is to have access to both categories and subcategories to be able to build the URI which will contain category_slug/subcategory_slug. Just to clarify both category_slug and subcategory_slug is the same column 'category_slug' but the dependency should kick in and separate the results.

Naincy Over a year ago

in select you can mention all the specific column names that you want to retrieve

AlexB Over a year ago

Let me update my question, so that you can see the DB structure, I think it will make more sense.

|

Collectives™ on Stack Overflow

Laravel DB query gives a set of results different from direct MySQL query

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related