PHP Integrated Query, a real LINQ library for PHP

Last update: May 3, 2022

PHP Integrated Query - Official site

Build status Code quality Coverage Status Stable Release License

What is PINQ?

Based off the .NET's LINQ (Language integrated query), PINQ unifies querying across arrays/iterators and external data sources, in a single readable and concise fluent API.

An example

$youngPeopleDetails = $people
        ->where(function ($row) { return $row['age'] <= 50; })
        ->orderByAscending(function ($row) { return $row['firstName']; })
        ->thenByAscending(function ($row) { return $row['lastName']; })
        ->take(50)
        ->indexBy(function ($row) { return $row['phoneNumber']; })
        ->select(function ($row) { 
            return [
                'fullName'    => $row['firstName'] . ' ' . $row['lastName'],
                'address'     => $row['address'],
                'dateOfBirth' => $row['dateOfBirth'],
            ]; 
        });

More examples

Installation

PINQ is compatible with >= PHP 7.3

Install the package via composer:

composer require timetoogo/pinq

GitHub

https://github.com/TimeToogo/Pinq
Comments
  • 1. Naming convention

    Hi, your project is still young so it's the best time to choose and use a naming convention. By there I mean that you should not, for example, call your function Where but where (you can see here that quite all frameworks work this way) (The only one I found to use PascalCase is ... Apache, whose style guide link is dead

    Check here for more informations and great stuff : http://www.phptherightway.com/

    Greetings, PunKeel

    Reviewed by punkeel at 2014-04-22 19:45
  • 2. Implement new static analysis tools and infrastructure for expression trees

    This provides the tools necessary to analyse a given expression tree.

    This branch currently supports:

    • Getting type and reflection information from almost all types of expressions.
    • Emulating PHP's native type system for all operators as of PHP 5.6
    • Extendable type system implementation with type data for date for date time classes, native interfaces (eg ArrayAccess), string / array / math internal functions and the ITraversable query API.

    Unsupported:

    • Dynamic language features such as variable function / method calls, variable class types...
    • Proper handling of references for all use cases (this is almost impossible in a language such as PHP)
    • Only a small subset of internal classes or functions type data is implemented.
    • User defined classes type data. Possible through utilising docblocks via phpDocumentor/ReflectionDocBlock
    Reviewed by TimeToogo at 2014-09-13 09:33
  • 3. An actual use-example

    Hey.

    I am working with Yii's 1.1.x version and I would like to extend CActiveRecord to implement Pinq.

    So I looked into the documentation but all I find is either a very long class list, or examples where the original classes with their implementation is not shown - or at least a pseudo implementation. There also seems to be no detail how Pinq is actually used by the developer.

    I am aware of this one: http://elliotswebsite.com/Pinq/api.html ...but it is just a method listing and an explanation of what is for what, but i can't make up an implementation from this.

    Could you maybe give a short example for a possible implementation? Thanks!

    Kind regards, Ingwie

    Reviewed by IngwiePhoenix at 2015-04-15 07:54
  • 4. Converting to different DSL

    Different data-sources, such as RDBMSs or MongoDB, use different DSLs (SQL/JS queries, etc).

    What I found out so far is this page in the docs, which seems to simulate usage of a visitor on different types of filtering/aggregation requests.

    It is really hard for newcomers to understand how to use the visitors to lazily evaluate Pinq expressions to convert them into, for example, SQL.

    A simplified SQL that appends conditionals to a given SQL string would probably be useful in those examples.

    Reviewed by Ocramius at 2014-04-22 23:16
  • 5. Hint on groupBy and select

    Hi there,

    I am trying to do some grouping on a dataset by "author" and would return a new dataset with "author" and its relevant count.

    The code is something like this:

    $filter1 = $data
            ->where(function($row)
            {
                return $row['price'] > 90 && $row['price'] < 99;
            })
            ->orderByDescending(function($row)
    {
        return $row['id'];
    });
    

    $filter1 returns the results where its price is between 90 and 99, perfect.

    Next:

    $filter2 = $filter1
            ->groupBy(function($row)
            {
                return $row['author'];
            })
            ->select(
            function(ITraversable $filter1)
    {
        return ['Author' => $filter1['author'], 'Count' => $filter1->count()];
    }
    );
    

    I would like to group the filtered dataset ($filter1) by "author" and get the count for each "author".

    I have some problem to set the select function body:

    return ['Author' => $filter1['author'], 'Count' => $filter1->count()];
    

    I am expecting to return an array with two keys ('Author' and 'Count') with respective author name and count, like:

    author1 12 author2 15 ...

    How can I achieve this?

    Reviewed by taylorren at 2014-06-15 06:55
  • 6. Need help on a mimic facet search implementation using Pinq

    Hi,

    I am trying to use Pinq to mimic a facet search and created a repo here: https://github.com/taylorren/pinq.

    The issue is that after creating a facet result, I am not able to display it as in my "demo1" route.

    Error message:

    Catchable fatal error: Argument 1 passed to pinqDemo{closure}() must be an instance of pinqDemo\ITraversable, instance of Pinq\Traversable given, called in F:\www\pinq\vendor\timetoogo\pinq\Source\Iterators\ProjectionIterator.php on line 40 and defined in F:\www\pinq\web\pinqDemo.php on line 17

    The function in question:

    function facetauthor($data)
        {
            $filter = $data
                    ->groupBy(function($row)
                    {
                        return $row['author'];
                    })
                    ->select(
                            function(ITraversable $data)
                    {
                        return ['author' => $data->first()['author'], 'count' => $data->count()];
                    }
                    );
    
            return $filter;
        }
    
    Reviewed by taylorren at 2014-06-29 02:07
  • 7. Performance of join and groupJoin methods is abnormally bad

    Your competitor here. :grinning:

    Pinq queries containing join and groupJoin can be thousands of times slower than native implementation, or any other LINQ library, when long arrays are joined (thousands of items). I have no idea what is going on, as I have trouble navigating your immense code base, but you should probably look into it.


    I've run the tests, your code is often an order of magnitude slower than YaLinqo, sometimes less, sometimes much more (like in the cases above). See gist with benchmark results (master branch). I've significantly improved performance of sorting thanks to your implementation. I didn't know array_multisort is so much faster than usort — the difference caused Pinq to be the fastest of the major LINQ libraries in the sorting test (well, no longer so, but it was).

    Considering your library is the only one providing LINQ to databases, I think it's critical to avoid performance traps in the basic functionality, even if the library is the slowest of the bunch.

    Technically, we aren't even competitors, considering YaLinqo is designed to be fast and expressive LINQ to objects and Pinq is designed to be LINQ to both objects and databases. Functionality and performance is so different that there isn't much to compete over. I'm still surprised by the lack of functionality in the basic methods of Pinq though, but I guess limiting expressiveness is required to make implementing query providers actually possible.

    What is the current state of MySQL query provider? Your website and readme say that it's a "demo", so I'm curious what plans you have on implementing production-ready query providers.

    By the way, how did you manage to receive more than 200 stars on GitHub? Did SitePoint cause so much traffic or is it something else?

    Anyway, thanks for writing your library. Ginq and Pinq forced me to update my own library: add missing methods, improve peformance, add integartion with CI and code quality tools. :grinning:

    Reviewed by Athari at 2015-06-08 18:33
  • 8. Document/Provide Functionality Without Expression Parsing

    Forgive me if I've missed something big, but it seems that IQueryable has a lot of methods that let you pass functions, and provides a nice eloquent interface. I would like to implement it in such a way that I can guarantee that no function parsing happens in userland php at runtime. Is there a reason why this would not be possible or why the lazy evaluation must use parsing at runtime? If not, is there a set of classes from which I should extend, or do I just satisfy the interfaces without inheriting?

    From what I can tell, the requests/filters/etc all need to be passed a function of a given spec, and then they should be able to just call the darn thing without needing to parse it and make a decision about what it means.

    Reviewed by winmillwill at 2014-09-03 21:20
  • 9. Replace hard coded classes with factories or DI container

    I want to use own Traversable class that adds a custom method, but it's impossible. It would be useful to have a environment object that can be passed to each iterator:

    $pinq = new Environment(['Traversable' => 'Vendor\Traversable', 'GroupedTraversable' => ...]);
    $pinq->traversable($data);
    
    Reviewed by hason at 2014-05-06 11:09
  • 10. Generate SQL query

    Has there been a way/method to generate SQL using PINQ? If not, I will have to go and code one. But I would like to know beforehand so I do not re-invent the wheel. :)

    Reviewed by IngwiePhoenix at 2015-07-03 20:55
  • 11. Only variable references should be returned by reference

    I get en error in IteratorGenerator, method &getIterator: Only variable references should be returned by reference. This should fix the problem. The error first occurred when I updated my server from php 5.6.7 to 5.6.8...

    $generator = $this->iteratorGenerator($this->iterator);
    return $generator;
    

    I'm not really sure what exactly is the problem here and if this is a good fix. Otherwise I could create a pullrequest.

    Reviewed by sidneywidmer at 2015-06-09 13:28
  • 12. Treat instances of DateTimeInterface as a value type throughout the API

    For instance:

    $traversable = Traversable::from([
        ['date' => new \DateTime('1/1/2000'), 'message' => '...'],
        ['date' => new \DateTime('1/1/2000'), 'message' => '...'],
        ['date' => new \DateTime('2/1/2000'), 'message' => '...'],
    ]);
    
    $results = $traversable
        ->groupBy(function ($row) { return $row['date']; })
        ->select(function (ITraversable $group, \DateTime $key) {
            return $key->format('d/m/Y') . ':' . $group->count(); 
        })
        ->asArray();
    

    Due to strict comparisons on the group by this would yield some rather unexpected results:

    ['01/01/2000:1', '01/01/2000:1', '02/01/2000:1']
    

    When what is actually wanted is:

    ['01/01/2000:2', '02/01/2000:1']
    

    This PR changes the Identity::hash behaviour such that DateTimes are treated as a value type based on their timestamps (and class name) which would solve this problem.

    Reviewed by TimeToogo at 2015-04-11 11:23
  • 13. Documentation should also be in the repository (to keep in sync with versions)

    The documentation is currently only in the gh-pages branch, which makes it quite easy for problems to popup if there are API breakages and the documentation isn't updated in sync.

    Instead, the doc should probably be put into the master branch (with the repo itself) and deployed into the gh-pages branch via a script.

    Reviewed by Ocramius at 2014-04-22 22:34
`LINQ to Object` inspired DSL for PHP

Ginq Array handling in PHP? Be happy with Ginq! Ginq is a DSL that can handle arrays and iterators of PHP unified. Ginq is inspired by Linq to Object,

Apr 17, 2022
A Collections library for PHP.

A Library of Collections for OO Programming While developing and helping others develop PHP applications I noticed the trend to use PHP's arrays in ne

May 2, 2022
🗃 Array manipulation library for PHP, called Arrayy!

?? Arrayy A PHP array manipulation library. Compatible with PHP 7+ & PHP 8+ \Arrayy\Type\StringCollection::create(['Array', 'Array'])->unique()->appen

May 8, 2022
Collections Abstraction library for PHP

Collections Collections Abstraction library for PHP The Collection library is one of the most useful things that many modern languages has, but for so

Dec 27, 2021
Library for (de-)serializing data of any complexity (supports JSON, and XML)

jms/serializer Introduction This library allows you to (de-)serialize data of any complexity. Currently, it supports XML and JSON. It also provides yo

May 19, 2022
:lipstick: Scalable and durable all-purpose data import library for publishing APIs and SDKs.
:lipstick: Scalable and durable all-purpose data import library for publishing APIs and SDKs.

Porter Scalable and durable data imports for publishing and consuming APIs Porter is the all-purpose PHP data importer. She fetches data from anywhere

May 26, 2022
[READ-ONLY] Collection library in CakePHP. This repo is a split of the main code that can be found in https://github.com/cakephp/cakephp

CakePHP Collection Library The collection classes provide a set of tools to manipulate arrays or Traversable objects. If you have ever used underscore

Mar 28, 2022
True asynchronous PHP I/O and HTTP without frameworks, extensions, or annoying code. Uses the accepted Fibers RFC to be implemented into PHP 8.1
True asynchronous PHP I/O and HTTP without frameworks, extensions, or annoying code. Uses the accepted Fibers RFC to be implemented into PHP 8.1

PHP Fibers - Async Examples Without External Dependencies True asynchronous PHP I/O and HTTP without frameworks, extensions, or annoying code behemoth

May 13, 2022
Map nested JSON structures onto PHP classes

JsonMapper - map nested JSON structures onto PHP classes Takes data retrieved from a JSON web service and converts them into nested object and arrays

May 13, 2022
A repository with implementations of different data structures and algorithms using PHP

PHP Data Structures and Algorithms Data structure and Algorithm is always important for any programming language. PHP, being one of the most popular l

May 17, 2022
Leetcode for PHP, five questions a week and weekends are updated irregularly
Leetcode for PHP,  five questions a week and weekends are updated irregularly

✏️ Leetcode for PHP why do you have to sleep for a long time ,and naturally sleep after death 联系 说明 由于目前工作主要是 golang,我又新起了一个LeetCode-Go-Week项目,- Leetc

May 26, 2022
Missing data types for PHP. Highly extendable.
Missing data types for PHP. Highly extendable.

Neverending data validation can be exhausting. Either you have to validate your data over and over again in every function you use it, or you have to rely it has already been validated somewhere else and risk potential problems.

Feb 7, 2022
JsonMapper - map nested JSON structures onto PHP classes

Takes data retrieved from a JSON web service and converts them into nested object and arrays - using your own model classes.

May 10, 2021
All Algorithms implemented in Php

The Algorithms - PHP All algorithms implemented in Php (for education) These implementations are for learning purposes. They may be less efficient tha

May 22, 2022
A community driven collection of sorting algorithms in PHP

algorithms A community driven collection of sorting algorithms This repository includes a comma separated file that includes 10k numbers between 1 and

May 16, 2022
Iterators - The missing PHP iterators.

PHP Iterators Description The missing PHP iterators. Features CachingIteratorAggregate ClosureIterator: ClosureIterator(callable $callable, array $arg

May 3, 2022
Yet Another LINQ to Objects for PHP [Simplified BSD]

YaLinqo: Yet Another LINQ to Objects for PHP Online documentation GitHub repository Features The most complete port of .NET LINQ to PHP, with many add

May 9, 2022
`LINQ to Object` inspired DSL for PHP

Ginq Array handling in PHP? Be happy with Ginq! Ginq is a DSL that can handle arrays and iterators of PHP unified. Ginq is inspired by Linq to Object,

Apr 17, 2022
MOP is a php query handling and manipulation library providing easy and reliable way to manipulate query and get result in a fastest way. ( WEBSITE VERSION )
MOP is a php query handling and manipulation library providing easy and reliable way to manipulate query and get result in a fastest way. ( WEBSITE VERSION )

Mysql Optimizer mysql optimizer also known as MOP is a php query handling and manipulation library providing easy and reliable way to manipulate query

Feb 14, 2022
MOP is a php query handling and manipulation library providing easy and reliable way to manipulate query and get result in a fastest way
MOP is a php query handling and manipulation library providing easy and reliable way to manipulate query and get result in a fastest way

Mysql Optimizer mysql optimizer also known as MOP is a php query handling and manipulation library providing easy and reliable way to manipulate query

Nov 20, 2021