2.4 (master) | 2.3 | 2.2 |
---|---|---|
./runtests.sh
run all unit tests./agile.sh
generate testdox documentation./coverage.sh
generate and open html coverageThe purpose of this project is to see how studiomado/query-bundle works and can be installed in a plain symfony project.
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
prompt> composer require studiomado/query-bundle
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)
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(),
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);
}
}
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 …
}
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:
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
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',
]
);
}
In Controller:
public function getTasksAction(Request $request)
{
return $this->getDoctrine()
->getRepository('AppBundle:Task')
->setRequest($request)
->findAllPaginated();
}
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"
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 |
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.