README

latest stable version

2.4 (master)	2.3	2.2

Run tests

./runtests.sh run all unit tests
./agile.sh generate testdox documentation
./coverage.sh generate and open html coverage

Plain symfony project for query-bundle

The purpose of this project is to see how studiomado/query-bundle works and can be installed in a plain symfony project.

database configuration
install query-bundle
create at least one entity

Database configuration

Remember to update parameter.yml file, parameter.yml.dist and config.yml file. In config.yml file also remember that the drive MUST be changed in pdo_sqlite to enable doctrine to work with this database.

This is just an example: for this example we use sqlite but in production you can use mysql or postgres or any other database supported by doctrine.

prompt> ./bin/console doctrine:database:create
Created database /path/to/project/var/data/data.sqlite for connection named default

Install query-bundle

prompt> composer require studiomado/query-bundle

Create at least one entity

Create at least one entity ...

prompt> ./bin/console doctrine:generate:entity

In this example I created an entity Task following command steps.

created ./src/AppBundle/Entity/
created ./src/AppBundle/Entity/Task.php
> Generating entity class src/AppBundle/Entity/Task.php: OK!
> > Generating repository class src/AppBundle/Repository/TaskRepository.php: OK!

... and update the schema ...

	prompt> ./bin/console doctrine:schema:update
	ATTENTION: This operation should not be executed in a production environment.
						 Use the incremental update to detect changes during development and use
						 the SQL DDL provided to manually update your database in production.

	The Schema-Tool would execute "1" queries to update the database.
	Please run the operation by passing one - or both - of the following options:
			doctrine:schema:update --force to execute the command
			doctrine:schema:update --dump-sql to dump the SQL statements to the screen

The schema update works only with force option

prompt> ./bin/console doctrine:schema:update --force
Updating database schema...
Database schema updated successfully! "1" query was executed

Just take a look of the database content (that now is simply empty).

prompt> ./bin/console doctrine:query:dql "select t from AppBundle:Task t"

The query will return an empty array of result

 array (size=0)
   empty

Just add first task ...

prompt> ./bin/console doctrine:query:sql "insert into task values (null, 'complete this guide', 'todo') "

and take a look of the content

prompt> ./bin/console doctrine:query:dql "select t from AppBundle:Task t"

array (size=1)
  0 =>
    object(stdClass)[507]
      public '__CLASS__' => string 'AppBundle\Entity\Task' (length=21)
      public 'id' => int 1
      public 'description' => string 'complete this guide' (length=19)
      public 'status' => string 'todo' (length=4)

Complete installation

First of all install vendors

prompt> composer require jms/serializer-bundle
prompt> composer require willdurand/hateoas-bundle
prompt> composer require white-october/pagerfanta-bundle
prompt> composer require friendsofsymfony/rest-bundle

and then, … add vendors in your app/AppKernel

new FOS\RestBundle\FOSRestBundle(),
new JMS\SerializerBundle\JMSSerializerBundle(),
new Bazinga\Bundle\HateoasBundle\BazingaHateoasBundle(),

Complete configuration and use the bundle

Once everything is done, you can add new endpoints using the query-bundle to query the database.

namespace AppBundle\Controller;

use Sensio\Bundle\FrameworkExtraBundle\Configuration\Route;
use Symfony\Bundle\FrameworkBundle\Controller\Controller;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpFoundation\Response;

class DefaultController extends Controller
{
    /** @Route("/", name="homepage") */
    public function indexAction(
        Request $request,
        \Doctrine\ORM\EntityManager $em,
        \JMS\Serializer\Serializer $serializer
    ) {
        $data = $em->getRepository('AppBundle:Task')
            ->setRequest($request)
            ->findAllPaginated();

        $content = $serializer->serialize($data, 'json');

        return new Response($content, 200);
    }
}

Configure your entity repository

Now be sure that your repository extends the right BaseRepository.

namespace AppBundle\Repository;

class TaskRepository extends \Mado\QueryBundle\Repositories\BaseRepository
{
    // to do …
}

namespace AppBundle\Entity;

/** @ORM\Entity(repositoryClass="AppBundle\Repository\TaskRepository") */
class Task
{
    // to do …
}

Customize entity serialization

Now if you want to customize responses add

use JMS\Serializer\Annotation as JMS;

On top of your entities and complete your JMS configurations. See JMS documentation to get all the complete documentation.

Here some examples:

Find All No Paginated

Added a new method in BaseRepository
When you need results applying filter and sort without pagination

public function findAllNoPaginated();

This feature was needed to create an Excel Report, injecting results into the Excel Report

Example without pagination

In Controller:

public function getTasksExcelReportAction(Request $request)
    {
        $tasks = $this->getDoctrine()
            ->getRepository('AppBundle:Task')
            ->findAllNoPaginated();
        
        $reportExcel = new TasksReport($tasks);
        $reportExcel->createReport();
        
        $excelContent = $reportExcel->printReport();
        
        return new Response(
            $excelContent,
            200, [
                'Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            ]
        );        
    }

Example with pagination

In Controller:

    public function getTasksAction(Request $request)
    {
        return $this->getDoctrine()
            ->getRepository('AppBundle:Task')
            ->setRequest($request)
            ->findAllPaginated();
    }

Queries

AND Conditions

If you want to create an AND condition with this library you can create it from the client for example with a simple GET request like this:

/api/foo?filtering[name|eq]=bar&filtering[surname|eq]=bar

This request will produce a query like this:

SELECT f0_.id AS id_0, f0_.name AS name_1, f0_.surname AS surname_2" .
FROM foo f0_" .
WHERE f0_.name = "bar" AND f0_.surname = "bar"

OR Conditions

If you want to create an OR condition with this library you can create it from the client for example with a simple GET request like this:

/api/foo?filtering_or[name|eq]=bar&filtering_or[surname|eq]=bar

This request will produce a query like this:

SELECT f0_.id AS id_0, f0_.name AS name_1, f0_.surname AS surname_2" .
FROM foo f0_" .
WHERE ((f0_.name = "bar" OR f0_.surname = "bar"))

Instead, if you want to have more OR conditions separated you can do something like this:

/api/foo?filtering_or[name|eq|1]=bar&filtering_or[surname|eq|1]=bar&filtering_or[group|contains|2]=baz&filtering_or[role|contains|2]=baz

This request will produce a query like this:

SELECT f0_.id AS id_0, f0_.name AS name_1, f0_.surname AS surname_2, f0_.group AS group_3, f0_.role AS role_4" .
FROM foo f0_" .
WHERE (f0_.name = "bar" OR f0_.surname = "bar") AND (f0_.group LIKE "%baz%" OR f0_.role LIKE "%baz%")

This can be done by using a counter after the operator separated by |

Search into relations

If you want to search inside an entity where a condition is inside another entity you can do this:

/api/users?filtering[_embedded.group.name|contains =bar

This request will produce a query like this:

SELECT u0_.id AS id_0 u0_.username AS username_1,  u0_.group_id AS group_id_2 " .
FROM User u0_
LEFT JOIN Group g1_ ON u0_.group_id = g1_.id " .
WHERE g1_.name LIKE "%bar%"

To do this you need to add inside the user entity some Hateoas annotations like this:

 * @Hateoas\Relation(
 *     "groups",
 *     embedded = @Hateoas\Embedded(
 *         "expr(object.getGroups())",
 *          exclusion = @Hateoas\Exclusion(
 *              excludeIf = "expr(object.getGroups().isEmpty() === true)",
 *              groups={"groups"},
 *              maxDepth = 1
 *          )
 *     ),
 *     exclusion = @Hateoas\Exclusion(
 *              excludeIf = "expr(object.getGroups().isEmpty() === true)",
 *              groups={"groups"},
 *              maxDepth = 1
 *          )
 * )

If you add Hateoas annotations correctly, you can search deeper than only "one level". Here an example:

/api/users?filtering[_embedded.profile.location.country.name|contains]=italy

In this example you search all users that have a profile with a country location name: Italy.
Profile, location and country are entities and name is the field.

You can use _embedded filter also into filtering_or conditions.

studiomado / query-bundle

Maintainers

Details

README

Run tests

Plain symfony project for query-bundle

Database configuration

Install query-bundle

Create at least one entity

Complete installation

Complete configuration and use the bundle

Configure your entity repository

Customize entity serialization

Find All No Paginated

Example without pagination

Example with pagination

Queries

AND Conditions

OR Conditions

Search into relations