Laravel select with condition for join

Question

I have 3 tables: Outputs, Products and Services in my Laravel 5 project.

I want to JOIN these 3 tables in one QUERY based for this condition:

if outputs.article_type = 'p' (p = product) get the products.name

if outputs.article_type = 's' (s = service) get the service.name

$outputs = Output::join('products', 'products.id', '=', 'outputs.article_id')
                   ... only if outputs.article_type is p (p=product)
                ->join('services', 'services.id', '=', 'outputs.article_id') 
                  ... only if outputs.article_type is s (s=service)
                ->select(array('outputs.output_at','---services or product---.name as article_name'))

lesssugar · Answer 1 · 2015-08-04T08:31:30.933

Not sure why you need to put the logic in one query. Seems like a perfect example for applying KISS.

Why not go for a function with 1 param and plain old IF to increase readability and make your (and maybe other devs) life easier? I don't see any advantage in what you're trying to do. Apart from brevity, which, in this case, is not the most important factor, I suppose.

In Output.php model:

    /**
     * Retrieves output of the given type.
     *
     * @param  string $type Type of the output
     *
     * @return mixed Object or false in case of problems
     */
    public static function getOutput($type) {

        if (!isset($type) || empty($type) || !in_array($type, ['p', 's'])) {
            // Wrong type
            return false;
        }

        if ($type === 'p') {
            $baseQuery = Output::join('products', 'products.id', '=', 'outputs.article_id');
            $table = 'products';
        } else {
            $baseQuery = Output::join('services', 'services.id', '=', 'outputs.article_id');
            $table = 'services';
        }

        return $baseQuery
                   ->select('outputs.output_at', $table.'.name as article_name')
                   ->get();
    }

Then, in a controller

$output = Output::getOutput('p');

if ($output && count($output)) {
    // Do other stuff or pass the data to a view...
}

Keep in mind that this is not tested and might need slightly different implementation than I showed, however the idea remains clear.

Thank you for your quick response. But I have a problem from that issue: http://stackoverflow.com/questions/31781436/datatable-filtering-searching-add-column-in-laravel-project. I can't search in my DataTables in a column add with add_column. I can do that if the data comes from SELECT QUERY. — Mario Ene, Aug 04 '15 at 08:33

score 0 · Answer 2 · edited May 23 '17 at 12:29

0

check this out A join with additional condition using Query Builder or ELoquent

but maybe you should consider eager loading instead of joining?

Output::with('Product', 'Service')->....

of course you need to add the relationships first

public function service() {
   if ($this->article_type == 's')
       return $this->belongsTo('Service', 'article_id', 'id')
   else
       return null;
 }

and overriding the name attribute to get the specific name you need

public functon getNameAttribute() {
   if ($this->service)
       return $this->service->name;
   else if ($this->product)
       return $this->product->name;
}

edited May 23 '17 at 12:29

Community

1
1

answered Aug 04 '15 at 08:33

NiRR

4,782
5
32
60

Thank you for the answer. But how can I call the name of the product or service in QUERY? ```Output::with('Product', 'Service')->select' ``` – Mario Ene Aug 04 '15 at 10:21
1

@MarioEne does [this](http://laravel-tricks.com/tricks/column-selection-in-eager-loading) explanation help? – NiRR Aug 04 '15 at 12:07
It's a good explanation for WITH QUERY for 1 table, but I have 2 tables involved: Products and Services. I'll try to extrapolate from your link. – Mario Ene Aug 04 '15 at 19:44

Laravel select with condition for join

2 Answers2