Viewed   81 times

Mysql query like this :

      'ifnull(SUM(case when location_code = ''',
      location_code ,
      ''' then quantity end),0) AS `',
      location_code , '`'
  ) INTO @sql
SET @sql = CONCAT('SELECT item_number,SUM(quantity) as "total_quantity", ', @sql, ' 
                  FROM item_details
                   GROUP BY item_number');

PREPARE stmt FROM @sql;


I want to convert it to laravel eloquent, but i'm confused. Because there exist many statement. There exist PREPARE, EXECUTE, SET, DEALLOCATE etc. You can see query above

How can I convert it to laravel eloquent?



It's mostly raw queries:

DB::table('item_details')->selectRaw('GROUP_CONCAT(...) INTO @sql')->get();
DB::statement('SET @sql = CONCAT(...)');
DB::statement('PREPARE stmt FROM @sql');
DB::statement('EXECUTE stmt');
DB::statement('DEALLOCATE PREPARE stmt');

Try this:

DB::table('item_details')->selectRaw('GROUP_CONCAT(...) INTO @sql')->get();
$sql = DB::selectOne('select @sql')->{'@sql'};
ItemDetails::select('item_number', DB::raw('SUM(quantity) as total_quantity'))
Sunday, October 23, 2022

You've included now your controller code. So in fact instead of

public function show(Invoice $invoice)
    $clients = Invoice::with('user')->get();
    $products = Invoice::with('products')->get();
    return view('', compact('invoice', $invoice),compact('clients',$clients))->with(compact('products',$products));

you can use only:

public function show(Invoice $invoice)
    return view('', compact('invoice', $invoice));

and in your view to display invoice products you can use:

@forelse ($invoice->products as $product) 
  {{ $product->name }}
   no products
Friday, August 12, 2022

Before I dig into multi table inheritance I want to lose a few words about single table inheritance. Single table inheritance is the more easy way when it comes to inheritance with db models.
You have multiple models bound to the same table and a type column to distinguish between the different model classes. However the reason you normally want to implement inheritance is because the models have shared properties but also some that are unique to the model.
When using single table inheritance your table looks similar to that at some point:

id   shared_column   question_column   article_column   question_column2   article_column2 etc...
1    Lorem           62                NULL             test               NULL
2    Ipsum           NULL              x                NULL               true

You end up having a lot of NULL values because there are columns not needed for a certain type of model. And with a lot of records this can influence the size of database.

However for some cases it might still be the best solution. Here's a well written Tutorial that shows how to implement it in Laravel in a pretty elegant way.

Multi Table Inheritance

Now lets look at multi table inheritance. With this method you split your single table into multiple ones (well I guess the name gave that kind of away already ;)) We are going to use a technique called Polymorphism

Our schema from the example above would look like this:

posts table:

id   shared_column  postable_id  postable_type
1    Lorem          1            Question
2    Ipsum          1            Article

questions table:

id   question_column   question_column2
1    62                test

articles table:

id   article_column   article_column2
1    x                true

A lot cleaner if you ask me...

The interesting columns here are postable_id and postable_type. The type tells us on which table we will find our "rest" of the model and the id specifies the primary key of the record that belongs to it. Note that the column names could be whatever you want, but it is convention to call it "-able".

Lets take a look at the Eloquent models now.


class Post extends Eloquent {
    // all your existing code
    public function postable(){
        return $this->morphTo();

Question / Article / every other postable type

class Question extends Post {
    public function post(){
        return $this->morphOne('Post', 'postable');

Note that you actually don't have to extend from Post but you can if you maybe have methods that you want to use too. Anyways, the polymorphic relationship will work with or without it.

Ok that's the basic setup. Here's how you can use your new models:

Retrieve all posts

$posts = Post::all();

Retrieve all questions

$questions = Question::all();

Get question columns from a post

$post = Post::find(1);
$question_column2 = $post->postable->question_column2;

Get post properties from question

$question = Question::find(1);
$shared_column = $question->post->shared_column;

Check which type a post is

$post = Post::find(1);
echo 'type: '.get_class($post->postable);
if($post->postable instanceof Question){
    // Looks like we got a question here

Create new question

Now bear with me, creating a model is a bit more complicated. If you have to do it at multiple places in your application I suggest you write a reusable function for it.

// create a record in the questions and posts table

$question = new Question();
$question->question_column = 'test';

$post = new Post();
$post->shared_column = 'New Question Post';

// link them together


So as you can see, a clean database comes with it's price. It will be a bit more complex to handle your model. However you can put all these extra logic (e.g. that's required to create a model) into a function in your model class and don't worry about it too much.

Also, there's a nice Tutorial for multi table inheritance with laravel too. Maybe it helps ;)

Tuesday, December 13, 2022

You have to supply the aggregate function in having clause, we can reuse the same one that we have in select like this

$brand = "MAX(CASE WHEN b.attribute_code = 'brand' then b.attribute_value END)";

$model = "MAX(CASE WHEN b.attribute_code = 'model' then b.attribute_value END)";

$category = "MAX(CASE WHEN b.attribute_code = 'category' then b.attribute_value END)";

$subcategory = "MAX(CASE WHEN b.attribute_code = 'subcategory' then b.attribute_value END)";

$brandName = 'honda';

$query = Item::selectRaw("a.number, a.description, {$brand} as brand, {$model} as model, {$category} as category, {$subcategory} as subcategory")
        ->from('items as a')
        ->join('attr_maps as b','b.number','=','a.number')
        ->havingRaw("{$brand} = ?", [$brandName])

return $query;

EDIT: After comments

You can execute for each params like this

$query = Item::selectRaw("a.number, a.description, {$brand} as brand, {$model} as model, {$category} as category, {$subcategory} as subcategory")
    ->from('items as a')
    ->join('attr_maps as b','b.number','=','a.number')

foreach($param as $key => $value) {
     $query = $query->havingRaw("{$$key} = ?", [$value]);

$results = $query->paginate(10);

return $results;
Thursday, October 27, 2022

Use fromQuery()

$query = IlluminateSupportFacadesDB::raw('select * from users');
$users = AppUser::fromQuery($query, []);
Sunday, October 16, 2022
Only authorized users can answer the search term. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :