PHP download

PHP code example of mash / mysql-json-serializer

1. Go to this page and download the library: Download mash/mysql-json-serializer library. Choose the download type require.

2. Extract the ZIP file and open the index.php.

3. Add this code to the index.php.

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

mash / mysql-json-serializer example snippets




use \Mash\MysqlJsonSerializer\QueryBuilder\Table\JoinStrategy\FieldStrategy;
use \Mash\MysqlJsonSerializer\Wrapper\FieldWrapper;
use \Mash\MysqlJsonSerializer\QueryBuilder\Table\Table;
use \Mash\MysqlJsonSerializer\Wrapper\Mapping;
use \Mash\MysqlJsonSerializer\QueryBuilder\QueryBuilder;
use \Mash\MysqlJsonSerializer\Service\TableManager;

$oneToManyTable = (new Table('advert_group', 'adg', 'adg_id'))
    ->addSimpleField('adg_id')
    ->addSimpleField('adg_name')
;

$table = (new Table('estate', 'est', 'est_id'))
    ->addSimpleField('est_id')
    ->addSimpleField('est_name')
    ->addOneToManyField($oneToManyTable, 'advert_groups', new FieldStrategy('adg_estate'));

$mapping = new Mapping();
$mapping
    ->addMap($table, 'est_id', 'id')
    ->addMap($table, 'est_name', 'name')
    ->addMap($oneToManyTable, 'adg_id', 'id')
    ->addMap($oneToManyTable, 'adg_name', 'name');

$builder = new QueryBuilder($table, new FieldWrapper($mapping, new TableManager()));
$builder
    ->setOffset(2)
    ->setLimit(1);

$sql = $builder->jsonArray();




use \Mash\MysqlJsonSerializer\QueryBuilder\Table\JoinStrategy\FieldStrategy;
use \Mash\MysqlJsonSerializer\Wrapper\FieldWrapper;
use \Mash\MysqlJsonSerializer\QueryBuilder\Table\Table;
use \Mash\MysqlJsonSerializer\Wrapper\Mapping;
use \Mash\MysqlJsonSerializer\QueryBuilder\QueryBuilder;
use \Mash\MysqlJsonSerializer\Service\TableManager;

$manyToOneTable = (new Table('estate', 'est', 'est_id'))
    ->addSimpleField('est_id')
    ->addSimpleField('est_name')
;

$table = (new Table('advert_group', 'adg', 'adg_id'))
    ->addSimpleField('adg_id')
    ->addSimpleField('adg_name')
    ->addManyToOneField($manyToOneTable, 'estate', new FieldStrategy('adg_estate'))
;

$mapping = new Mapping();
$mapping
    ->addMap($manyToOneTable, 'est_id', 'id')
    ->addMap($manyToOneTable, 'est_name', 'name')
    ->addMap($table, 'adg_id', 'id')
    ->addMap($table, 'adg_name', 'name');

$builder = new QueryBuilder($table, new FieldWrapper($mapping, new TableManager()));
$builder
    ->setOffset(2)
    ->setLimit(2);

$sql = $builder->jsonArray();




use \Mash\MysqlJsonSerializer\QueryBuilder\Field\CrossReference\Reference;
use \Mash\MysqlJsonSerializer\QueryBuilder\Table\JoinStrategy\ReferenceStrategy;
use \Mash\MysqlJsonSerializer\QueryBuilder\Field\CrossReference\Pair;
use \Mash\MysqlJsonSerializer\Wrapper\FieldWrapper;
use \Mash\MysqlJsonSerializer\QueryBuilder\Table\Table;
use \Mash\MysqlJsonSerializer\Wrapper\Mapping;
use \Mash\MysqlJsonSerializer\QueryBuilder\QueryBuilder;
use \Mash\MysqlJsonSerializer\Service\TableManager;

$photo  = new Table('photo', 'pht', 'pht_id');
$advert = (new Table('advert', 'adv', 'adv_id'))
    ->addSimpleField('adv_id')
    ->addSimpleField('adv_type')
;

$reference = new Table('photo_xref', 'xrf');
$strategy  = new ReferenceStrategy(
    new Reference(
        new Pair($advert, 'adv_id'),
        new Pair($reference, 'xref_adv_id')
    ),
    new Reference(
        new Pair($photo, 'pht_id'),
        new Pair($reference, 'xref_pht_id')
    )
);

$mapping = new Mapping();
$mapping
    ->addMap($advert, 'adv_id', 'id')
    ->addMap($advert, 'adv_type', 'type')
    ->addMap($photo, 'pht_id', 'id')
    ->addMap($photo, 'pht_hash', 'hash')
;

$builder = new QueryBuilder($advert, new FieldWrapper($mapping, new TableManager()));
$builder->setLimit(2);

$photo
    ->addSimpleField('pht_id')
    ->addSimpleField('pht_hash')
;

$advert->addManyToManyField($photo, 'photos', $strategy);

$sql = $builder->jsonArray();




use \Mash\MysqlJsonSerializer\QueryBuilder\Table\JoinStrategy\FieldStrategy;
use \Mash\MysqlJsonSerializer\Wrapper\FieldWrapper;
use \Mash\MysqlJsonSerializer\QueryBuilder\Table\Table;
use \Mash\MysqlJsonSerializer\Wrapper\Mapping;
use \Mash\MysqlJsonSerializer\QueryBuilder\QueryBuilder;
use \Mash\MysqlJsonSerializer\Service\TableManager;

$oneToOneTable = (new Table('page', 'pge', 'pge_id'))
    ->addSimpleField('pge_id')
    ->addSimpleField('pge_url')
;

$table = (new Table('advert', 'adv', 'adv_id'))
    ->addSimpleField('adv_id')
    ->addSimpleField('adv_type')
    ->addManyToOneField($oneToOneTable, 'page', new FieldStrategy('adv_page'))
;

$mapping = new Mapping();
$mapping
    ->addMap($oneToOneTable, 'pge_id', 'id')
    ->addMap($oneToOneTable, 'pge_url', 'url')
    ->addMap($table, 'adv_id', 'id')
    ->addMap($table, 'adv_type', 'type');

$builder = new QueryBuilder($table, new FieldWrapper($mapping, new TableManager()));
$builder
    ->setOffset(2)
    ->setLimit(2);

$expected = "SELECT JSON_ARRAYAGG(JSON_OBJECT('id',adv_res.adv_id,'type',adv_res.adv_type,'page',(SELECT JSON_OBJECT('id',pge.pge_id,'url',pge.pge_url) FROM page pge WHERE pge.pge_id = adv_res.adv_page LIMIT 1))) FROM (SELECT * FROM advert adv  LIMIT 2 OFFSET 2) adv_res";

$sql = $builder->jsonArray();




namespace App\Entity;

use App\Entity\Location\City;
use Doctrine\ORM\Mapping as ORM;
use JMS\Serializer\Annotation as Serializer;
use Mash\MysqlJsonSerializer\Annotation as MysqlJSON;
use Mash\MysqlJsonSerializer\Annotation\Table;
use Symfony\Component\Validator\Constraints as Assert;

/**
 * @ORM\Table(name="page", indexes={
 *     @ORM\Index(columns={"pge_status"}),
 *     @ORM\Index(columns={"pge_site"}),
 * })
 *
 * @ORM\Entity(repositoryClass="App\Repository\PageRepository")
 *
 * @Serializer\ExclusionPolicy(Serializer\ExclusionPolicy::ALL)
 *
 * @Table(alias="pge")
 */
class Page implements LockedResourceInterface
{
    const STATUS_PENDING = 0;

    const STATUS_NOT_PARSED = 1;

    const STATUS_PARSED = 2;

    const STATUS_INVALID = 3;

    public static $types = [
        self::STATUS_PENDING,
        self::STATUS_NOT_PARSED,
        self::STATUS_PARSED,
        self::STATUS_INVALID,
    ];

    /**
     * @ORM\Id
     * @ORM\GeneratedValue
     * @ORM\Column(name="pge_id", type="integer")
     *
     * @Serializer\Expose
     *
     * @MysqlJSON\Expose
     */
    private $id;

    /**
     * @ORM\Column(name="pge_status", type="integer")
     *
     * @Serializer\Expose
     *
     * @MysqlJSON\Expose
     */
    private $status;

    /**
     * @ORM\Column(name="pge_type", type="string")
     *
     * @Serializer\Expose
     *
     * @MysqlJSON\Expose
     */
    private $type;

    /**
     * @ORM\Column(name="pge_category", type="string")
     *
     * @Serializer\Expose
     *
     * @MysqlJSON\Expose
     */
    private $category;

    /**
     * @ORM\Column(name="pge_url", type="string")
     *
     * @Serializer\Expose
     *
     * @MysqlJSON\Expose
     */
    private $url;

    /**
     * One Page has one Lock.
     *
     * @ORM\OneToOne(targetEntity="App\Entity\Lock\PageLock", mappedBy="resource")
     *
     * @Serializer\Expose
     *
     * @MysqlJSON\Expose
     */
    private $lock;

    /**
     * @ORM\Column(name="pge_body", type="blob", nullable=true)
     *
     * @Serializer\Expose
     * @Serializer\Type("string")
     * @Serializer\AccessType("public_method")
     *
     * @Serializer\Groups(groups={"Default", "page_full"})
     *
     * @Assert\Type(type="string")
     *
     * @MysqlJSON\Expose(groups={"Default", "page_full"}, type="Mash\MysqlJsonSerializer\Wrapper\Type\Blob")
     */
    private $body;

    /**
     * Many Pages have one Style.
     *
     * @ORM\ManyToOne(targetEntity="App\Entity\Style", inversedBy="pages", cascade={"persist"})
     * @ORM\JoinColumn(name="pge_style", referencedColumnName="stl_id", nullable=true)
     *
     * @Serializer\Expose
     * @Serializer\Groups(groups={"page_full"})
     *
     * @MysqlJSON\Expose(groups={"page_full"})
     */
    private $style;

    /**
     * Many Pages have one City.
     *
     * @var City
     *
     * @ORM\ManyToOne(targetEntity="App\Entity\Location\City", cascade={"persist"})
     * @ORM\JoinColumn(name="pge_city", referencedColumnName="cit_id")
     *
     * @Serializer\Expose
     * @Serializer\Groups(groups={"page_full"})
     *
     * @Assert\NotBlank
     *
     * @MysqlJSON\Expose(groups={"page_full"})
     */
    private $city;

    /**
     * Many Pages have one Site.
     *
     * @var Site
     *
     * @ORM\ManyToOne(targetEntity="App\Entity\Site", cascade={"persist"})
     * @ORM\JoinColumn(name="pge_site", referencedColumnName="site_id")
     *
     * @Serializer\Expose
     * @Serializer\Groups(groups={"page_full"})
     *
     * @Assert\NotBlank
     *
     * @MysqlJSON\Expose(groups={"page_full"})
     */
    private $site;

    //....
}




namespace App\RestController;

use App\Annotation\Lock;
use App\Entity\Lock\PageLock;
use App\Entity\Page;
use App\Entity\Site;
use App\Form\PageType;
use FOS\RestBundle\Controller\AbstractFOSRestController;
use FOS\RestBundle\Controller\Annotations as Rest;
use Mash\MysqlJsonSerializer\QueryBuilder\SQL\SQL;
use Mash\MysqlJsonSerializer\Service\QueryBuilderFactory;
use Nelmio\ApiDocBundle\Annotation\ApiDoc;
use Sensio\Bundle\FrameworkExtraBundle\Configuration\IsGranted;
use Symfony\Component\HttpFoundation\Request;

/**
 * Class PageController.
 *
 * @Rest\RouteResource("page", pluralize=false)
 */
class PageController extends AbstractFOSRestController
{
    /**
     * @var QueryBuilderFactory
     */
    private $queryBuilderFactory;

    public function __construct(QueryBuilderFactory $queryBuilderFactory)
    {
        $this->queryBuilderFactory = $queryBuilderFactory;
    }

    /**
     * Get Page list.
     *
     * @ApiDoc(
     *     views={"v1"},
     *     section="Page",
     *     description="Get Page list",
     *     filters={
     *         {
     *             "name": "page",
     *             "dataType": "integer",
     *             "     *
     * @return SQL
     */
    public function getListAction(Request $request)
    {
        $page   = $request->get('page', 1);
        $limit  = $request->get('limit', 20);
        $siteId = $request->get('site', null);
        $status = $request->get('status', null);
        $noLock = $request->get('noLock', null);

        $builder = $this->queryBuilderFactory->getBuilder(Page::class);
        $builder->orderBy('pge.pge_id', 'DESC');

        if (null !== $status) {
            $builder
                ->andWhere('pge.pge_status = :status')
                ->setParameter('status', $status)
            ;
        }

        if (null !== $siteId && 0 !== (int) $siteId) {
            $builder
                ->innerJoin(Site::class, 'pge.pge_site = sit.site_id AND sit.site_id = :site')
                ->setParameter('site', $siteId);
        }

        if ('1' === $noLock) {
            $builder
                ->select('pge.*, lck.lck_id')
                ->leftJoin(
                    PageLock::class,
                    'pge.pge_id = lck.lck_resource AND lck.lck_type = :lock_type'
                )
                ->setParameter('lock_type', 'page')
                ->andWhere('lck.lck_id is NULL')
            ;
        }

        return $builder->jsonPagination($page, $limit);
    }

    /**
     * Get Page by ID.
     *
     * @ApiDoc(
     *     views={"v1"},
     *     section="Page",
     *     description="Get Page by ID",
     *




namespace Mash\MysqlJsonSerializer\Wrapper\Type;

class Blob implements CustomTypeInterface
{
    public function convert(string $name, string $alias): string
    {
        return "CONVERT({$alias}.{$name} USING utf8mb4)";
    }
}




/**
 * @ORM\Entity(repositoryClass="App\Repository\EstateRepository")
 *
 * @Serializer\ExclusionPolicy(Serializer\ExclusionPolicy::ALL)
 *
 * @Table(alias="est", map={
 *     "last_update": {
 *         "route": "App\Entity\AdvertGroup.App\Entity\Advert[updated_at]",
 *         "type": Mash\MysqlJsonSerializer\QueryBuilder\Field\JoinField::TYPE_MAX,
 *         "groups": {"estate_public_list"},
 *     },
 *     "max_rooms": {
 *         "route": "App\Entity\AdvertGroup.App\Entity\Advert[rooms]",
 *         "type": Mash\MysqlJsonSerializer\QueryBuilder\Field\JoinField::TYPE_MAX,
 *         "groups": {"estate_public_list"},
 *     },
 *     "min_rooms": {
 *         "route": "App\Entity\AdvertGroup.App\Entity\Advert[rooms]",
 *         "type": Mash\MysqlJsonSerializer\QueryBuilder\Field\JoinField::TYPE_MIN,
 *         "groups": {"estate_public_list"},
 *     },
 *     "max_area": {
 *         "route": "App\Entity\AdvertGroup.App\Entity\Advert[area]",
 *         "type": Mash\MysqlJsonSerializer\QueryBuilder\Field\JoinField::TYPE_MAX,
 *         "groups": {"estate_public_list"},
 *     },
 *     "min_area": {
 *         "route": "App\Entity\AdvertGroup.App\Entity\Advert[area]",
 *         "type": Mash\MysqlJsonSerializer\QueryBuilder\Field\JoinField::TYPE_MIN,
 *         "groups": {"estate_public_list"},
 *     },
 *     "max_sell_price": {
 *         "route": "App\Entity\AdvertGroup.App\Entity\Advert[price]",
 *         "type": Mash\MysqlJsonSerializer\QueryBuilder\Field\JoinField::TYPE_MAX,
 *         "groups": {"estate_public_list"},
 *         "filter": {"type": "sell"},
 *     },
 *     "min_sell_price": {
 *         "route": "App\Entity\AdvertGroup.App\Entity\Advert[price]",
 *         "type": Mash\MysqlJsonSerializer\QueryBuilder\Field\JoinField::TYPE_MIN,
 *         "groups": {"estate_public_list"},
 *         "filter": {"type": "sell"},
 *     },
 *     "max_rent_price": {
 *         "route": "App\Entity\AdvertGroup.App\Entity\Advert[price]",
 *         "type": Mash\MysqlJsonSerializer\QueryBuilder\Field\JoinField::TYPE_MAX,
 *         "groups": {"estate_public_list"},
 *         "filter": {"type": "rent"},
 *     },
 *     "min_rent_price": {
 *         "route": "App\Entity\AdvertGroup.App\Entity\Advert[price]",
 *         "type": Mash\MysqlJsonSerializer\QueryBuilder\Field\JoinField::TYPE_MIN,
 *         "groups": {"estate_public_list"},
 *         "filter": {"type": "rent"},
 *     },
 *     "address": {
 *         "route": "App\Entity\AdvertGroup.App\Entity\Advert[address]",
 *         "type": Mash\MysqlJsonSerializer\QueryBuilder\Field\JoinField::TYPE_FIRST,
 *         "orderBy": "id",
 *         "groups": {"estate_public_list"},
 *     },
 *     "advert_count": {
 *         "route": "App\Entity\AdvertGroup.App\Entity\Advert[id]",
 *         "type": Mash\MysqlJsonSerializer\QueryBuilder\Field\JoinField::TYPE_COUNT,
 *         "groups": {"estate_public_list"},
 *     },
 *     "rent_description": {
 *         "route": "App\Entity\AdvertGroup.App\Entity\Advert[description]",
 *         "type": Mash\MysqlJsonSerializer\QueryBuilder\Field\JoinField::TYPE_FIRST,
 *         "groups": {"estate_public_list"},
 *         "filter": {"type": "rent"},
 *     },
 *     "sell_description": {
 *         "route": "App\Entity\AdvertGroup.App\Entity\Advert[description]",
 *         "type": Mash\MysqlJsonSerializer\QueryBuilder\Field\JoinField::TYPE_FIRST,
 *         "groups": {"estate_public_list"},
 *         "filter": {"type": "sell"},
 *     },
 *     "type": {
 *         "route": "App\Entity\AdvertGroup.App\Entity\Advert[type]",
 *         "type": Mash\MysqlJsonSerializer\QueryBuilder\Field\JoinField::TYPE_FIRST,
 *         "groups": {"estate_public_list"},
 *     },
 *     "category": {
 *         "route": "App\Entity\AdvertGroup.App\Entity\Advert[category]",
 *         "type": Mash\MysqlJsonSerializer\QueryBuilder\Field\JoinField::TYPE_FIRST,
 *         "groups": {"estate_public_list"},
 *     },
 *     "daily": {
 *         "route": "App\Entity\AdvertGroup.App\Entity\Advert[daily]",
 *         "type": Mash\MysqlJsonSerializer\QueryBuilder\Field\JoinField::TYPE_MAX,
 *         "groups": {"estate_public_list"},
 *     },
 *     "sell_contacts_ids": {
 *         "route": "App\Entity\AdvertGroup.App\Entity\Advert.App\Entity\Contact[id]",
 *         "type": Mash\MysqlJsonSerializer\QueryBuilder\Field\JoinField::TYPE_COLLECTION,
 *         "groups": {"estate_public_list"},
 *         "filter": {"App\Entity\Advert[type]": "sell"},
 *     },
 *     "rent_contacts_ids": {
 *         "route": "App\Entity\AdvertGroup.App\Entity\Advert.App\Entity\Contact[id]",
 *         "type": Mash\MysqlJsonSerializer\QueryBuilder\Field\JoinField::TYPE_COLLECTION,
 *         "groups": {"estate_public_list"},
 *         "filter": {"App\Entity\Advert[type]": "rent"},
 *     },
 *     "photo_files_ids": {
 *         "route": "App\Entity\AdvertGroup.App\Entity\Advert.App\Entity\Photo.App\Entity\GoogleFile[file_id]",
 *         "type": Mash\MysqlJsonSerializer\QueryBuilder\Field\JoinField::TYPE_COLLECTION,
 *         "groups": {"estate_public_list"},
 *     },
 * })
 */
class Estate
{
}