2017 © Pedro Peláez
 

project symfony-rowmapper

A RowMapper and QueryBuilder, makes querying databases easier and better than orm

image

chrisandchris/symfony-rowmapper

A RowMapper and QueryBuilder, makes querying databases easier and better than orm

  • Wednesday, April 12, 2017
  • by chrisandchris
  • Repository
  • 1 Watchers
  • 2 Stars
  • 2,480 Installations
  • PHP
  • 0 Dependents
  • 0 Suggesters
  • 0 Forks
  • 2 Open issues
  • 17 Versions
  • 0 % Grown

The README.md

A QueryBuilder and passive record ORM for Symfony2

Build Status Code Climate Test Coverage Version Downloads Licence, (*1)

Despite it's package name, it's not simply a row mapper. And it's not simply for Symfony. This project is a QueryBuilder and a Mapper for SQL Result Sets, both combined but still very separated, so you can use them independent., (*2)

<?php

use ChrisAndChris\Common\RowMapperBundle\Services\Model\ConcreteModel;

class DemoRepo {
    /** @var ConcreteModel  */
    private $model;

    public function __construct(ConcreteModel $model){
        $this->model = $model;
    }

    public function getCustomerName($customerId) {
        $query = $this->model->getDependencyProvider()->getBuilder()->select()
            ->field('customer_name')
            ->table('customer')
            ->where()
                ->field('customer_id')->equals()->value($customerId)
            ->close()
            ->getSqlQuery();

        return $this->model->runWithFirstKeyFirstValue($query);
    }
}

This doc gives a short overview of all the possibilities this package provides. We are moving the contents continuously to the doc/ directory, so look more detailed information up there., (*3)

Was it does

  • Opens and handles MySQL-Connection
  • Provides a simple interface for building prepared statements and querying the database
  • Provides a simple interface for mapping the results to classes

Internals

The basic internal principal is the following:, (*4)

  • There are Types (simple key-value classes) which represent a part of a statement
  • The query gets parsed using a Parser and the same-named snippets (they contain sql)
  • The query is returned

How To Use

 Configuration

Configure your symfony2 project as you do always. The bundle uses the database information stored in the parameters.yml and automatically connects to the given database., (*5)

Actually, there is no further configuration possible., (*6)

A simple query

Let's create a service definition:, (*7)

services:
    project.demo_repo:
        class: DemoRepo
        arguments: ['@common_rowmapper.model']

Create the repository:, (*8)

<?php

use ChrisAndChris\Common\RowMapperBundle\Services\Model\ConcreteModel;

class DemoRepo {
    /** @var ConcreteModel  */
    private $model;

    public function __construct(ConcreteModel $model){
        $this->model = $model;
    }

    public function getCustomerName($customerId) {
        $query = $this->model->getDependencyProvider()->getBuilder()->select()
            ->field('customer_name')
            ->table('customer')
            ->where()
                ->field('customer_id')->equals()->value($customerId)
            ->close()
            ->getSqlQuery();

        return $this->model->runWithFirstKeyFirstValue($query);
    }
}

If you want to map a more complicated query to a class, use something like this:, (*9)

<?php

use ChrisAndChris\Common\RowMapperBundle\Entity\Entity;

class CustomerEntity implements Entity {
    public $customerId;
    public $name;
    public $street;
    public $zip;
    public $city;
}

And to map, use this method, (*10)

<?php

use ChrisAndChris\Common\RowMapperBundle\Services\Model\ConcreteModel;

class DemoModel {

    /** @var ConcreteModel  */
    private $model;

    public function __construct(ConcreteModel $model){
        $this->model = $model;
    }

    public function getCustomer($customerId) {
        $query = $this->model->getDependencyProvider()->getBuilder()->select()
            ->fieldlist([
                'customer_id' => 'customerId',
                'cus_name' => 'name',
                'street',
                'zip',
                'city'
            ])
            ->table('customer')
            ->where()
                ->field('customer_id')->equals()->value($customerId)
            ->close()
            ->getSqlQuery();

        return $this->model->run($query, new SomeEntity());
    }
}

Some more information

The field() method

You could use an array for separating database, table, field:, (*11)

field(['database', 'table', 'field'])`

If you fetch single fields, you must append a comma by yourself:, (*12)

->field('field1')->c()
->field('field2')->c()

You could also give a closure as parameter:, (*13)

->field(function () { return $value; });

The value() method

Use this method to append a parameter to the query:, (*14)

->value($someValue);
->value(function () { return $someValue; });

The fieldlist() method

This method is even much more powerful, use it as follows:, (*15)

Simple key-value usage:, (*16)

fieldlist([
    'field' => 'alias',
    'customer_id' => 'customerId',
    'cus_name' => 'name'
])

Specify database, table, field:, (*17)

fieldlist([
    'database:table:field' => 'alias'
]);

Mix anything, (*18)

fieldlist([
    'database:table:field' => 'alias',
    'field1', // fetched by real field name
    'field2' => 'alias1'
]);

The f(), where(), order(), groupBy()

Any of these four types open so-called "braces". A brace represents a kind of sub-query which is fully independent from the query before. In its internals, during parsing this sub-query, the parser has principally no access to the other statements., (*19)

So, if you finish one of these, simply call close() or end() to close the brace:, (*20)

->where()
    ->field('field1')->equals()->value(true)
->close()

The raw()

Because of the lack of time and to fulfill any requirement, I simply implemented a raw method. And gladly, this method is able to use parameters :D, (*21)

->raw('SELECT customer_id FROM customer WHERE customer_name LIKE ?', [
    '%github%'
]);

The in()

You can simply build IN-clauses with the two following methods:, (*22)

// option a
->in([1, 2, 3, 4, 5, 6])
// option b
->in()
    ->select()
    ->value(1)
->close()

Option A uses prepared statements all-the-way, any value within the array gets is way as a parameter to the database., (*23)

Conditional appending

There are three methods to provide conditional appending: * _if() * _else() * _end(), (*24)

You are allowed to nest ifs, and you are allowed to push a closure as parameter to the if:, (*25)

->_if($condition === true)
    ->where()
    ->_if(function() { return $condition === true; })
        // ...
    ->_end()
        // ...
    ->close()
->_else()
    //
->_end()

Some other methods

  • f() - for functions
  • where() - build wheres
  • any() - a god-blessed star (evil SELECT *)
  • value() - a parameter
  • null() - a sql NULL
  • isNull() - compares to null using IS NULL
  • join() - join tables
  • using() - using clause for joined tables
  • on() - on clause for joined tables
  • union() - create UNION statements
  • asLong() - creating while loop
  • each() - creating each loop

The Versions

12/04 2017

dev-master

9999999-dev

A RowMapper and QueryBuilder, makes querying databases easier and better than orm

  Sources   Download

GPL-2.0

The Requires

 

The Development Requires

orm database postgres symfony query mysql pgsql querybuilder builder rowmapper

12/04 2017

v2.1.5

2.1.5.0

A RowMapper and QueryBuilder, makes querying databases easier and better than orm

  Sources   Download

GPL-2.0

The Requires

 

The Development Requires

orm database postgres symfony query mysql pgsql querybuilder builder rowmapper

12/04 2017

dev-bugfix-in-clause

dev-bugfix-in-clause

A RowMapper and QueryBuilder, makes querying databases easier and better than orm

  Sources   Download

GPL-2.0

The Requires

 

The Development Requires

orm database postgres symfony query mysql pgsql querybuilder builder rowmapper

15/03 2017

v2.1.4

2.1.4.0

A RowMapper and QueryBuilder, makes querying databases easier and better than orm

  Sources   Download

GPL-2.0

The Requires

 

The Development Requires

orm database postgres symfony query mysql pgsql querybuilder builder rowmapper

15/03 2017

dev-dev

dev-dev

A RowMapper and QueryBuilder, makes querying databases easier and better than orm

  Sources   Download

GPL-2.0

The Requires

 

The Development Requires

orm database postgres symfony query mysql pgsql querybuilder builder rowmapper

27/02 2017

v2.1.3

2.1.3.0

A RowMapper and QueryBuilder, makes querying databases easier and better than orm

  Sources   Download

GPL-2.0

The Requires

 

The Development Requires

orm database postgres symfony query mysql pgsql querybuilder builder rowmapper

03/12 2016

v2.1.2

2.1.2.0

A RowMapper and QueryBuilder, makes querying databases easier and better than orm

  Sources   Download

GPL-2.0

The Requires

 

The Development Requires

orm database postgres symfony query mysql pgsql querybuilder builder rowmapper

15/09 2016

dev-target/2.2.0

dev-target/2.2.0

A RowMapper and QueryBuilder (PgSQL and MySQL), makes querying databases easier and better than orm

  Sources   Download

GPL-2.0

The Requires

 

The Development Requires

orm database postgres symfony query mysql pgsql querybuilder builder rowmapper

29/06 2016

v2.1.1

2.1.1.0

A RowMapper and QueryBuilder, makes querying databases easier and better than orm

  Sources   Download

GPL-2.0

The Requires

 

The Development Requires

orm database postgres symfony query mysql pgsql querybuilder builder rowmapper

05/02 2016

v2.1.0

2.1.0.0

A RowMapper and QueryBuilder, makes querying databases easier and better than orm

  Sources   Download

GPL-2.0

The Requires

 

The Development Requires

orm database symfony query querybuilder builder rowmapper