Force Index on a query about ideas

[adelf]

In complex queries with with() previous solution doesn't work. Better to overwrite base query 'from' value instead:

    $query = SomeModel::where()->with()...->orderBy();

    $query->getQuery()->from(\DB::raw($query->getQuery()->from . ' FORCE INDEX (index_name)'));
    // or just
    $query->getQuery()->from(\DB::raw('`table` FORCE INDEX (index_name)'));

    $results = $query->get();

[martianoff]

My solution:

Add following to the model class:

Class SomeModel extends Model {

    public static function IndexRaw($index_raw)
    {
        $model = new static();
        $model->setTable(\DB::raw($model->getTable() . ' ' . $index_raw));
        return $model;
    }

    ...
}

Now you can use simply something like this:

SomeModel::IndexRaw('FORCE INDEX (some_index_name)')->where('condition','=',true)->get();

[thril]

I needed the FORCE INDEX for a join, so I used the following solution...

DB::table('table1 as t1')
    ->join(
        DB::raw('table2 as t2 FORCE INDEX (index_name)'),
        't1.some_field', '=', 't2.some_field'
    )
    ...

[ahmedash95]

so you need something like Model::forceIndex('index_name')->get(); ?

[danielbaylis]

Yes. This would save needing to use DB::raw and hard code the table name.

[ahmedash95]

i will make a PR for it , hoping taylor merge it :D

[tomschlick]

Make sure to link back to this discussion so he can see the reasoning behind it. Seems reasonable to me 👍

[gocanto]

if I am not mistaking, you can achieve this performing using models relations, as so:

$users = Loan::has('installments')->with(['user' => function ($query) use ($q){
            $query->select(['id', 'first_name', 'last_name']);
            $query->where('verified', 'yes');
            $query->where('status', 'active');
            $query->where('role', '!=', 'admin');
            if ($q != '') {
                $query->whereRaw("CONCAT (first_name, ' ', last_name) like '%" . $q . "%'");
            }
            return $query;
        }])->where('status', '!=', 'paid')->groupBy('user_id')->get();

this is a relly specific example, but I thought it could illustrate my thoughts

[gcphost]

@ahmedash95 did you ever make a PR? Would like to see this missing feature.

[danielbaylis]

I don't believe this has become a feature. It would be important that it worked on all possible database drivers and I am not sure if they all support specifying an index by name?

[ahmedash95]

i'm agree with @danielbaylis , any idea about what should we do ( let it as a DB::row() solution or try to make a PR for that ) ?

[gcphost]

What about a beforeWhere()?

[harshsanghani]

I also want to user FORCE INDEX with laravel Eloquent so Please message here if you implement that.