Start new Query Builder on Model's table

Use a scope. Yes better suited for eloquent, but you can use QB, db facade, or getPdo() in a scope.

@JLRDW - Interesting idea. A little quirky, but interesting. Are you sure there is no built-in method doing that?

Raw in what way? Would something like $query->toArray() work for you? Or perhaps collect($query->toArray())? Could be used in a query scope like @jlrdw suggested.

@NASH - As an example, I needed to get some stats, using groupBy and some raw things inside get(). It’s actually weird that it still returns models after using groupBy.

I had done this a while back, not used, but just answering a question:

   public function scopecountPets($query, $petsearch = '')
    {

        if (ChkAuth::userRole('admin') === 'admin') {
            $sql = "SELECT COUNT(petid) as total FROM dc_pets WHERE petname LIKE :sch";
            $params = [':sch' => $petsearch . "%"];
            
        } else {
            $userid = Auth::user()->id;
            $sql = "SELECT COUNT(petid) as total FROM dc_pets WHERE petname LIKE :sch AND ownerid = :userid";
            $params = [':sch' => $petsearch . "%", ':userid' => $userid];
        }
        $sth = DB::getPdo()->prepare($sql);
        $sth->execute($params);
        $kount = $sth->fetch(\PDO::FETCH_OBJ);
        return $kount->total;
    }

Using getPdo(). The trick is to use scope ahead of method name:

public function scopecountPets($query, $petsearch = '')

Then in controller you just:

$krows = Pet::countPets($petsearch);

Of course don't forget use statement:

use App\Pet;

$query may be optional in this case, I just put it in anyway.

@JLRDW - Why would you go so low level and use PDO though?

@ROBOROBOK - Active record (eloquent) converts to normal sql, pdo at runtime.

And actually you have it backwards, active record (shortcut language) is low level, PDO with the proper bindings is high level.

@JLRDW - Eee no? Eloquent is higher level that PDO, as it uses PDO under the surface.

You said something about getTable() but that just returns the name of the table? You could fetch the data as an array if you don't want Eloquent models or turn the array into an instance of Illuminate\Support\Collection. What do you expect the output to look like?

Please try to re-read my question if you’d like to try to answer.

Eloquent is just active record. Doctrine or hibernate would be high level.

What are you after? And why won't a scope work. Just use repository then.

https://github.com/bestmomo/laravel5-5-example

@JLRDW - Scope will work, it’s just a little awkward, as it’s meant to narrow the query, not to do something completely different. But yeah, from practical point of view it will work.

I completely don’t get your understanding of high/low level. Eloquent is just as much high level as Doctrine. You use some weird definition. If class A serves to give more power to class B, it means that class A is higher level than class B. And that is clearly the case with Eloquent vs. PDO. The latter one is just a library to use SQL in a more convenient manner. Eloquent use composition relationship on PDO, so how can PDO be more high level?

Active record is no different that linq-to-sql (shortcut language).

So any active record "query" Has to convert to parent sql, pdo.

It was originally meant as an "easy" shortcut. In many cases it is. However in a complex query especially with joins and group by and order by just writing a regular query is better.

A good article: https://www.yiiframework.com/wiki/2541/when-to-use-active-record

Also nesting master - detail, is fine for a few records, but if detail has thousands I wouldn't even consider active record.

@JLRDW - You answered yourself - Active Record is used to make it easier. That also means it’s higher level. I really understand how it works, just think your naming convention is backwards.

@ROBOROBOK - Also don't confuse active record and query builder like:

$quy = Powner::query()->leftJoin('dc_pets', 'dc_powners.ownerid', '=', 'dc_pets.ownerid')
                ->select('dc_powners.ownerid', 'dc_powners.oname')
                ->selectRaw('count(dc_pets.petid) as countOfPets')
                ->groupby('dc_powners.ownerid')
                ->orderby('dc_powners.oname')
                ->get();

That's query builder code, efficient. Can use eloquent or QB.

Okay, let’s go back to my original question, because we are going nowhere with that debate.

I notice now that you are calling getTable() only to get the name of the table for DB statement...difficult to see with all the parenthesis.

Anyways, calling getQuery() on the Eloquent model should get you the underlying Illuminate\Database\Query\Builder instance (as opposed to the Illuminate\Database\Eloquent\Builder instance). So something like $model->getQuery()->get() would result in a "pure" collection as you call it (an instance of Illuminate\Support\Collection).

I tried using query() method (as I said in the original post), but it just starts new query, still being Eloquent Query. Not sure if getQuery() method exists, I will check it out.

new MyModel

Why would you need a new instance, doesn't make sense.

@JLRDW - To call a non-static method on it. How else would you get a table name?

@ROBOROBOK - getQuery() exists on the Eloquent builder instance (Illuminate\Database\Eloquent\Builder), query() exists on Illuminate\Database\Eloquent\Model and returns a new instance of \Illuminate\Database\Eloquent\Builder (which, in turn, uses Illuminate\Database\Query\Builder as its underlying builder).

A basic example to visualize:

// Returns "Illuminate\Database\Eloquent\Builder"
dd( get_class(App\User::query()) );

// Returns "Illuminate\Database\Query\Builder"
dd( get_class(App\User::getQuery()) );

// Returns "Illuminate\Support\Collection"
dd( get_class(App\User::getQuery()->get()) );

I experimented a little and yes - the answer is getQuery()! It works perfectly fine (example using Invoice model):

$stats = Invoice::getQuery()
    ->whereBetween(DB::raw('date(issued_at)'), [$from, $to])
    ->groupBy('date')
    ->get([
        DB::raw('date(issued_at) as date'),
        DB::raw('sum(total_value) as total_value_sum')
    ]);

So it does exactly the same thing as replacing Invoice::getQuery() with DB::table((new Invoice)->getTable()).

They both, after using get() in the end, return an instance of Illuminate\Support\Collection and I can avoid weird transformation on the model itself. Selecting raw grouped values on the model feels just wrong.

Thanks @nash !