Jun 12 2015

Encapsulating complex query conditions using QueryCriteria in Laravel

Category: Laravel,PHPFractalizeR @ 9:56 pm

Very often my query conditions in repositories become complicated. To be able to reuse them across repositories I use the following simple pattern:

interface CriteriaInterface
{
    /**
     * @param EloquentBuilder|QueryBuilder $query
     *
     * @return void
     */
    public function attach($query);
}

All my query conditions implement this interface and accept condition parameters in the constructor. For example, this is how CategoryCriteria looks like:

/**
 * Class CategoryCriteria
 *
 */
class CategoryCriteria implements CriteriaInterface
{
    /**
     * @var null|int|array
     */
    private $categoryId;
 
    /**
     * CategoryCriteria constructor.
     *
     * @param null|int|array $categoryId
     */
    public function __construct($categoryId)
    {
        $this->categoryId = $categoryId;
    }
 
    /**
     * @param null|int|array $categoryId
     *
     * @return LocationCriteria
     */
    public static function create($categoryId)
    {
        return new static($categoryId);
    }
 
    /**
     * @param EloquentBuilder|QueryBuilder $query
     */
    public function attach($query)
    {
        if (empty($this->categoryId)) {
            return;
        }
 
        if (is_array($this->categoryId)) {
            $query->whereIn('category_id', $this->categoryId);
        } else {
            $query->where('category_id', $this->categoryId);
        }
    }
}


Jun 05 2015

Laravel’s Eloquent::has() and Eloquent::whereHas() performance problems

Category: Laravel,PHPFractalizeR @ 12:52 am

I had a task in my project where I needed to fetch all orders from the database, for which some user has posted an offer. The first intention was to use nice and shiny whereHas method on Eloquent builder like this:

Order::whereHas('offers', function($q) {
    $q->where('user_id', 1112485)
 });

 

But something stopped me and I went to investigate. It turned out, that this instruction generates the following SQL statement:

SELECT *
FROM orders
WHERE (SELECT count(*)
       FROM offers
       WHERE offers.order_id = orders.id AND "user_id" = 1112485) >= 1;

 

The plan for this statement is:

Seq Scan on orders (cost=0.00..249278.84 rows=9525 width=912) (actual time=271.528..465.900 rows=2 loops=1)
 Filter: ((SubPlan 1) >= 1)
 Rows Removed by Filter: 28574
 SubPlan 1
 -> Aggregate (cost=8.44..8.45 rows=1 width=0) (actual time=0.009..0.011 rows=1 loops=28576)
 -> Index Only Scan using offers_order_id_user_id_idx on offers (cost=0.42..8.44 rows=1 width=0) (actual time=0.004..0.004 rows=0 loops=28576)
 Index Cond: ((order_id = orders.id) AND (user_id = 1112485))
 Heap Fetches: 2
Planning time: 0.133 ms
Execution time: 465.953 ms

Pay attention to query cost and execution time. The same task may be achieved by  the following query:

SELECT *
FROM orders
WHERE id IN (SELECT DISTINCT order_id
             FROM offers
             WHERE user_id = 1112485)

 

And this one has much better plan:

Nested Loop (cost=0.71..6280.39 rows=5 width=912) (actual time=0.043..5.260 rows=2 loops=1)
 -> Unique (cost=0.42..6238.76 rows=5 width=4) (actual time=0.024..5.217 rows=2 loops=1)
 -> Index Only Scan using offers_order_id_user_id_idx on offers (cost=0.42..6238.62 rows=53 width=4) (actual time=0.019..5.201 rows=2 loops=1)
 Index Cond: (user_id = 1112485)
 Heap Fetches: 2
 -> Index Scan using orders_pkey on orders (cost=0.29..8.31 rows=1 width=912) (actual time=0.007..0.009 rows=1 loops=2)
 Index Cond: (id = offers.order_id)
Planning time: 0.191 ms
Execution time: 5.312 ms

Well, about 100x difference, right?

The same goes for simple

Order::has('offers')

 

Generated SQL for it is

SELECT *
FROM "orders"
WHERE (SELECT count(*)
       FROM "offers"
       WHERE "offers"."order_id" = "orders"."id") >= 1

 

With the query plan

Seq Scan on orders (cost=0.00..856965.30 rows=9525 width=912) (actual time=11.044..2987.939 rows=25603 loops=1)
 Filter: ((SubPlan 1) >= 1)
 Rows Removed by Filter: 2973
 SubPlan 1
 -> Aggregate (cost=29.71..29.72 rows=1 width=0) (actual time=0.095..0.097 rows=1 loops=28576)
 -> Index Only Scan using offers_order_id_user_id_idx on offers (cost=0.42..29.68 rows=13 width=0) (actual time=0.019..0.072 rows=9 loops=28576)
 Index Cond: (order_id = orders.id)
 Heap Fetches: 247734
Planning time: 0.260 ms
Execution time: 3039.399 ms

If you rewrite query like this

EXPLAIN ANALYZE
SELECT *
FROM "orders" O
WHERE id IN (SELECT DISTINCT order_id
             FROM offers);

 

You get the following plan:

Hash Join (cost=14061.99..21990.07 rows=20785 width=849) (actual time=940.164..1093.802 rows=28694 loops=1)
 Hash Cond: (o.id = offers.order_id)
 -> Seq Scan on orders o (cost=0.00..7439.19 rows=32119 width=849) (actual time=0.006..51.002 rows=32119 loops=1)
 -> Hash (cost=13802.18..13802.18 rows=20785 width=4) (actual time=939.942..939.942 rows=28694 loops=1)
 Buckets: 4096 Batches: 1 Memory Usage: 1009kB
 -> HashAggregate (cost=13594.33..13802.18 rows=20785 width=4) (actual time=852.582..898.002 rows=28694 loops=1)
 Group Key: offers.order_id
 -> Seq Scan on offers (cost=0.00..12887.66 rows=282666 width=4) (actual time=0.003..422.209 rows=282666 loops=1)
Planning time: 0.364 ms
Execution time: 1133.665 ms

3x improvement. Which is slightly better 🙂

So, the conclusion is always to check what is generated for you by ORM.


May 27 2015

Disabling Eager Loading Eloquent Model Relations in Laravel 4 and 5

Category: Laravel,Laravel 5FractalizeR @ 1:12 pm

Since accidental lazy-loading almost always lead to performance problems, I was seeking a way to disable it. The solution is easy. Just define somewhere the following trait and use it in your model when you need to disable lazy loading:

* Class OnlyEagerLoadedModelTrait
 *
 * @package FH\Stock\Model
 */
trait EagerLoadOnlyTrait
{
    /**
     * @param string $method
     */
    protected function getRelationshipFromMethod($method)
    {
        $modelName = static::class;
        throw new LogicException(
            "EagerLoadedOnlyTrait: Attempting to lazy-load relation '$method' on model '$modelName'"
        );
    }
}

 

class CategoryUser extends Eloquent
{
    use EagerLoadOnlyTrait;
}

Tags: , , ,


Jun 20 2014

Laravel4: Calls to Config::get() return empty values

Category: Articles,Laravel,PHPFractalizeR @ 2:05 am

I’ve made one mistake developing my service provider. Here it is:

 

class AuthorizationServiceProvider extends ServiceProvider
{
 
    /**
     * Indicates if loading of the provider is deferred.
     *
     * @var bool
     */
    protected $defer = false;
 
    /**
     * Bootstrap the application events.
     *
     * @return void
     */
    public function boot()
    {
        $this->package('fractalizer/authorization', 'frauth');
    }
 
    /**
     * Register the service provider.
     *
     * @return void
     */
    public function register()
    {
        $this->app->bind(
            'fractalizer.authorization',
            function () {
                return new Manager();
            }
        );
 
        \Route::filter(\Config::get('frauth::aliases.role_filter_name'), '\Fractalizer\Authorization\Filter@hasRole');
    }
}

 

 

If you make even a single c all to Config::get() inside register() method of your service provider, all the next calls to your configuration namespace will return empty data. The correct way to do things is like this:

class AuthorizationServiceProvider extends ServiceProvider
{
 
    /**
     * Indicates if loading of the provider is deferred.
     *
     * @var bool
     */
    protected $defer = false;
 
    /**
     * Bootstrap the application events.
     *
     * @return void
     */
    public function boot()
    {
        $this->package('fractalizer/authorization', 'frauth');
        \Route::filter(\Config::get('frauth::aliases.role_filter_name'), '\Fractalizer\Authorization\Filter@hasRole');
    }
 
    /**
     * Register the service provider.
     *
     * @return void
     */
    public function register()
    {
        $this->app->bind(
            'fractalizer.authorization',
            function () {
                return new Manager();
            }
        );
    }
}

E.g. move all the calls to Config into boot() method.