timetoogo/pinq.demo.sql

A documented demo of the PINQ DSL query provider platform

Maintainers

Details

github.com/TimeToogo/Pinq.Demo.Sql

Homepage

Source

Issues

Installs: 11

Dependents: 0

Suggesters: 0

Security: 0

Stars: 10

Watchers: 5

Forks: 1

Open Issues: 1

dev-master 2015-08-01 13:36 UTC

Requires

Requires (Dev)

MIT 6250296e3996cb0518e306ad33c0051f4be16c4f

  • Elliot Levin <elliotlevin.woop@hotmail.com>

mysqldemopinq

This package is auto-updated.

Last update: 2024-10-25 06:41:29 UTC

README

This is a demo of using the query provider functionality of PINQ. This library is based on PINQ V3 and demonstrates the power of the DSL query provider API. This is a proof of concept and should not be run in production.

Overview

This demo written in under 2 KLOC and implements a small section of the PINQ query API mapping it to a MySQL database backend. Tables are treated as collections and rows are represented as associative arrays.

use Pinq\Demo\Sql\DB;

$db = new DB($pdo);

$results = $db->table('customers')
    ->where(function ($row) { return $row['age'] <= 50; })
    ->orderByAscending(function ($row) { return $row['firstName']; })
    ->thenByAscending(function ($row) { return $row['lastName']; })
    ->take(50)
    ->select(function ($row) {
        return [
            'fullName'    => $row['firstName'] . ' ' . $row['lastName'],
            'address'     => $row['address'],
            'dateOfBirth' => $row['dateOfBirth'],
        ];
    })
    ->asArray()

The above query will be converted to a SQL equivalent:

SELECT
CONCAT(CONCAT(customers.firstName, ' '), customers.lastName) AS fullName,
customers.address AS address,
customers.dateOfBirth AS dateOfBirth
FROM (
    SELECT * FROM
    (
        SELECT * FROM
        (SELECT * FROM customers) AS customers
        WHERE (customers.age <= 50)
    ) AS customers
    ORDER BY customers.firstName ASC, customers.lastName ASC
    LIMIT 50 OFFSET 0
) AS customers

This query will be executed against the supplied PDO connection and the result set will be returned. As this is just a demo the SQL generation is fairly makeshift and does not produce the most efficient code possible.

More examples can be found in the test suite

API Support

Scope (filtering) API

  • ->where(function) = WHERE <expr>
  • ->orderBy(function, direction), ->orderByAscending(function), ->orderByDescending(function) = ORDER BY <expr> ASC|DESC
  • ->unique() = DISTINCT
  • ->take(amount), ->skip(amount), ->slice(skip, take) = LIMIT <amount> OFFSET <amount>
  • ->select(function) = SELECT <expr> AS <alias>...

Request (data retrieval) API

  • ->asArray(), ->getIterator(), ->getTrueIterator(), ->asTraversable(), ->asCollection() = SELECT * FROM

Operation (data mutation) API

  • ->apply(function) = UPDATE <table> SET <column> = <expr>...
  • ->clear() = DELETE FROM <table>

PHP Expression Syntax

  • Binary operators: +, -, *, /, %, ==, !=, &&, ||, >, >=, <, <=, .
  • Unary operators: !, ~, -, +
  • isset and empty
  • Ternary: ?:
  • Functions: strlen, md5

Documentation

This repository is an example of how the PINQ query provider can be implemented. For more details you can browse the repository or follow the links below.