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.


Mar 20 2012

84 PHP functions for disable_functions directive

Category: PHPFractalizeR @ 12:57 pm

A list of php function functions, disabled on the servers I configured:
Continue reading “84 PHP functions for disable_functions directive”


Nov 08 2011

Autoloading PEAR incompatible classes, installed from PEAR packages

Category: Articles,PHPFractalizeR @ 12:42 pm

I like PEAR distribution system. It’s simple to use and installed packages are globally available to all your server scripts (of course, this is an advantage only when you mean it to be). But to easily use packages, installed into PEAR, they should follow some conventions, which are rather old these days, when PSR-0 is out there.

I installed Monolog library using PEAR into my system and found, that it hasn’t bundled autoloader. So, I cannot just require() one file in Monolog and start using it, after it has been installed into PEAR. I need to configure my autloader. And to configure my autoloader, I need to know the exact path of PEAR installation.

The best way I found so far to do that is stream_resolve_include_path() function. So, I’ve done that like this:

Continue reading “Autoloading PEAR incompatible classes, installed from PEAR packages”


Nov 11 2010

Splitting string at capitalized letters

Category: Articles,Code snippets,PHP,PHPFractalizeR @ 7:42 pm

Recently I had a task to split a given string at capitalized characters, having those saved in PHP. I’ve invented the following solution:

<?php
$str = 'TestMyFuncCall';
var_dump(preg_split('/(?=[A-Z])/', $str, null, PREG_SPLIT_NO_EMPTY));

The result is:

array(4) {
  [0]=>
  string(4) "Test"
  [1]=>
  string(2) "My"
  [2]=>
  string(4) "Func"
  [3]=>
  string(4) "Call"
}

I utilize regular expressions assertions here to avoid character loss.


Nov 08 2010

Practising OOP in PHP5: emulating mixins (Ruby-like)

Category: Articles,PHPFractalizeR @ 12:25 am

Some evening I was implementing behavorial patterns in my framework’s ORM and I desperately needed something like Ruby mixin or C# extension method or like trait/graft in PHP6+. So I was curious to see how can I implement mixins in PHP. If you don’t know what mixin is, I will tell you at once.

So, I invite you to follow me on paths to implementing mixins in PHP and programming a small library to support them. This article targets beginner and average level programmers. Just be sure you know what OOP is. During the process, I will also make some small mistake about nuances of PHP 5.3 work with classes, will point to it and suggest my solution to it. Good reading!

Continue reading “Practising OOP in PHP5: emulating mixins (Ruby-like)”

Tags: , ,


Sep 27 2010

Regular expression for russian letters in UTF-8

Category: Articles,Code snippets,PHP,PHPFractalizeR @ 1:27 pm
(\xD0[\x80-\xBF])|(\xD1[\x80-\xBF])


Jan 06 2010

Free opensource Symfony-friendly IDE: NetBeans 6.8

Category: Articles,PHPFractalizeR @ 8:03 pm

My search for a good PHP IDE never stops 😉 My choice was Zend Studio until recently. But Zend Studio is not free and it’s free analogue – PDT – lacks some features like refactoring (PDT is also mainly developed by Zend, so, I think, Zend will always keep it low to prevent competition with Zend Studio). Also, it is monstrous (More than 300Mb installer) and it definitely lacks good Symfony support.

Recently I came across NetBeans, which is now free and developed by Sun. Sun seem to abandon all own IDE projects in favour of NetBeans, so this is a somehow strong foundation.

Oh… 25Mb installer only for PHP version 😉 And… it is easy to use. Very. I would like to guide you through the creation of new symfony project in NetBeans.

Continue reading “Free opensource Symfony-friendly IDE: NetBeans 6.8”

Tags: , ,


Jan 04 2010

[Linux, PHP] PHP script to backup all databases

Category: Articles,Linux administration,PHPFractalizeR @ 1:20 am

Hello.

I’d like to share my script, which I use on some production servers for quick database backup using mysql tools. People say, that I should use bash for that, but I prefer PHP 🙂

Script uses mysqldump for dumping databases and gzip for compressing backups. rm is run to ensure backups are not stored for too long.

Continue reading “[Linux, PHP] PHP script to backup all databases”

Tags: , , ,


Aug 27 2008

[PHP] PHP Quine

Category: Articles,PHPFractalizeR @ 4:28 pm

Browing internet, I found the term of “quine” which is the program, that prints it’s exact source code. I was curious, if I am able to write such software. This is what I was able to compose:

<?php
$u='$';
$q="'";
$qq='"';
$s='<?php';
$e='?>';
$w='eval($p);';
$p='echo"$s\r\n{$u}u=$q$u$q;\r\n{$u}q=$qq$q$qq;\r\n{$u}qq=$q$qq$q;\r\n{$u}s=$q$s$q;\r\n{$u}e=$q$e$q;\r\n{$u}w=$q$w$q;\r\n{$u}p=$q$p$q;\r\n$w";';
eval($p);

It is a small and quite simple quine, however, I spent about several hours understanding how quines work and how to write them in PHP 🙂

Tags: ,


Aug 25 2008

[PHP, MySQL] Batch changing charset and collation on databases

Category: Articles,MySQL,PHPFractalizeR @ 11:51 pm

Recently I needed to quickly change charset on all tables and change each field’s chanset in each table in a specific database to latin1 and collation to latin1_swedish_ci. I googled a little and found this solution by shimon doodkin. I used it and it did work, but also it attempted to change charset at MySQL information_schema system database which is something I didn’t want to. It also proceessed all databases at MySQL server. Luckily, there was only two 🙂 It was a test server, that’s why I was uncareful.

Ok. I wrote a bit more elegant solution I would like to share with you today. It is pretty self-explanatory and is based on ALTER TABLE CONVERT TO charset MySQL statement.

Continue reading “[PHP, MySQL] Batch changing charset and collation on databases”

Tags: , , , , ,


Aug 18 2008

[PHP] Writting Custom Class Autoloader Using SPL

Category: Articles,PHPFractalizeR @ 1:11 am

My approach to writting mid-to-huge applications is to put each class to separate file and autoload class files on demand to eliminate the use of include/require statements completely. Autoloading in some cases can even speed up your project (for example, my scripts rarely throw exceptions. Exception classes are loaded by demand and only in case exception happend, so I remove

<?php
require_once('DatabaseException.php');
require_once('UserBOException.php);

overhead from my project. However, writting a good universal autoloader class is not trivial, however, simple. In this article I would like to suggest my own lightweigh multifunctional autoloader class.

Continue reading “[PHP] Writting Custom Class Autoloader Using SPL”

Tags: , , ,


Aug 18 2008

[PHP] PDO: Wrapping and making sweet

Category: Articles,PHPFractalizeR @ 12:18 am

Now PDO is most promising database access mechanism in PHP5. However, using PDO natively is not always convinient. In this article I will suggest my own lightweight wrapper for PDO calls, which simplyfies things for me. I am PHP5 fan, so we will use all object oriented technologies this language can give us in constructing our PDO wrapper.

Continue reading “[PHP] PDO: Wrapping and making sweet”

Tags: , , , , , , , ,