Skip to content

Instantly share code, notes, and snippets.

@chanmix51

chanmix51/SsObjectMap.php

Last active October 1, 2015 08:47
Show Gist options
  • Save chanmix51/1957498 to your computer and use it in GitHub Desktop.
Save chanmix51/1957498 to your computer and use it in GitHub Desktop.
Download ZIP
Object queries with Pomm
Raw
application.php
<?php
$loader = require __DIR__."/vendor/.composer/autoload.php";
$loader->add('YourDb', __DIR__);
$database = new Pomm\Connection\Database(array('dsn' => 'pgsql://user:pass@host/your_db'));
$map = $database
->createConnection()
->getMapFor('YourDb\PublicSchema\SsObject');
$database->registerConverter(
'SsObject',
new Pomm\Converter\PgEntity($database, 'YourDb\PublicSchema\SsObject'),
array($map->getTableName())
);
foreach($map->getSatellitesOf('soleil') as $planet)
{
printf(" %10s | %9d | %d satellites\n", $planet['name'], $planet['distance'], count($planet['satellites']));
foreach($planet['satellites'] as $satellite)
{
printf(" | | %10s \n", $satellite['name']);
}
printf("------------------+--------------+------------\n");
}
Raw
gistfile1.txt
<?php
[...]
foreach($map->getSatellitesOf('soleil') as $planet)
{
printf(" %10s | %9d | %d satellites\n", $planet['name'], $planet['distance'], count($planet['satellites']));
foreach($planet['satellites'] as $satellite)
{
printf(" | | (%d) %10s \n", $satellite['_extra'][0], $satellite['name']);
}
printf("------------------+--------------+------------\n");
}
/*
mercure | 57909176 | 0 satellites
------------------+--------------+------------
venus | 108208930 | 0 satellites
------------------+--------------+------------
terre | 149597887 | 1 satellites
| | (1) lune
------------------+--------------+------------
mars | 227936637 | 0 satellites
------------------+--------------+------------
jupiter | 778412027 | 4 satellites
| | (1) io
| | (2) europe
| | (3) ganymède
| | (4) callisto
------------------+--------------+------------
saturne | 1421179772 | 9 satellites
| | (1) mimas
| | (2) encelade
| | (3) thétys
| | (4) dioné
| | (5) rhéa
| | (6) titan
| | (7) hypérion
| | (8) japet
| | (9) phœbé
------------------+--------------+------------
uranus | 2147483647 | 5 satellites
| | (1) miranda
| | (2) ariel
| | (3) umbriel
| | (4) titania
| | (5) obéron
------------------+--------------+------------
neptune | 2147483647 | 3 satellites
| | (1) protée
| | (2) triton
| | (3) néréide
------------------+--------------+------------
pluton | 2147483647 | 1 satellites
| | (1) charon
------------------+--------------+-----------
Raw
query1.sql
SELECT
planet.name,
planet.distance,
planet.is_satellite_of,
array_agg(satellite) AS satellites
FROM
public.ss_object planet
LEFT JOIN public.ss_object satellite ON
planet.name = satellite.is_satellite_of
WHERE
planet.is_satellite_of = 'soleil'
GROUP BY
planet.name,
planet.distance,
planet.is_satellite_of
ORDER BY
planet.distance ASC
;
name | distance | is_satellite_of | satellites
---------+------------+-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
mercure | 57909176 | soleil | {NULL}
venus | 108208930 | soleil | {NULL}
terre | 149597887 | soleil | {"(lune,384399,terre)"}
mars | 227936637 | soleil | {NULL}
jupiter | 778412027 | soleil | {"(callisto,1882700,jupiter)","(ganymède,1070400,jupiter)","(europe,671100,jupiter)","(io,421800,jupiter)"}
saturne | 1421179772 | soleil | {"(phœbé,12944000,saturne)","(japet,3561300,saturne)","(hypérion,1481100,saturne)","(titan,1221830,saturne)","(rhéa,527040,saturne)","(dioné,377400,saturne)","(thétys,294660,saturne)","(encelade,238020,saturne)","(mimas,185520,saturne)"}
uranus | 2876679082 | soleil | {"(obéron,583520,uranus)","(titania,435910,uranus)","(umbriel,266300,uranus)","(ariel,191020,uranus)","(miranda,129390,uranus)"}
neptune | 4503443661 | soleil | {"(néréide,5513400,neptune)","(triton,354800,neptune)","(protée,117647,neptune)"}
pluton | 5906450638 | soleil | {"(charon,17181,pluton)"}
(9 lignes
Raw
query2.sql
WITH
ss_satellites AS (
SELECT
name,
distance,
is_satellite_of,
row_number() OVER (PARTITION BY is_satellite_of ORDER BY distance ASC) AS ranking
FROM
ss_object
ORDER BY
distance DESC
)
SELECT
planet.name,
planet.distance,
planet.is_satellite_of,
array_agg(satellite) AS satellites
FROM
public.ss_object planet
LEFT JOIN ss_satellites satellite ON
planet.name = satellite.is_satellite_of
WHERE
planet.is_satellite_of = 'soleil'
GROUP BY
planet.name,
planet.distance,
planet.is_satellite_of
ORDER BY
planet.distance ASC
;
name | distance | is_satellite_of | satellites
---------+------------+-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
mercure | 57909176 | soleil | {NULL}
venus | 108208930 | soleil | {NULL}
terre | 149597887 | soleil | {"(lune,384399,terre,1)"}
mars | 227936637 | soleil | {NULL}
jupiter | 778412027 | soleil | {"(callisto,1882700,jupiter,4)","(ganymède,1070400,jupiter,3)","(europe,671100,jupiter,2)","(io,421800,jupiter,1)"}
saturne | 1421179772 | soleil | {"(phœbé,12944000,saturne,9)","(japet,3561300,saturne,8)","(hypérion,1481100,saturne,7)","(titan,1221830,saturne,6)","(rhéa,527040,saturne,5)","(dioné,377400,saturne,4)","(thétys,294660,saturne,3)","(encelade,238020,saturne,2)","(mimas,185520,saturne,1)"}
uranus | 2876679082 | soleil | {"(obéron,583520,uranus,5)","(titania,435910,uranus,4)","(umbriel,266300,uranus,3)","(ariel,191020,uranus,2)","(miranda,129390,uranus,1)"}
neptune | 4503443661 | soleil | {"(néréide,5513400,neptune,3)","(triton,354800,neptune,2)","(protée,117647,neptune,1)"}
pluton | 5906450638 | soleil | {"(charon,17181,pluton,1)"}
Raw
ss_object.sql
--
-- PostgreSQL database dump
--
SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET search_path = test, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: ss_object; Type: TABLE; Schema: test; Owner: -; Tablespace:
--
CREATE TABLE ss_object (
name character varying NOT NULL,
distance bigint,
is_satellite_of character varying,
CONSTRAINT exclusive_null CHECK ((((distance IS NULL) AND (is_satellite_of IS NULL)) OR ((distance IS NOT NULL) AND (is_satellite_of IS NOT NULL)))),
CONSTRAINT ss_object_distance_check CHECK ((distance >= 0))
);
--
-- Data for Name: ss_object; Type: TABLE DATA; Schema: test; Owner: -
--
COPY ss_object (name, distance, is_satellite_of) FROM stdin;
soleil \N \N
mercure 57909176 soleil
venus 108208930 soleil
terre 149597887 soleil
moon 384399 terre
mars 227936637 soleil
phobos 9377 mars
deimos 23460 mars
jupiter 778412027 soleil
io 421800 jupiter
ganymède 1070400 jupiter
callisto 1882700 jupiter
europe 671100 jupiter
saturne 1421179772 soleil
titan 1221870 saturne
thétys 294992 saturne
dioné 377400 saturne
japet 3560840 saturne
mimas 185600 saturne
encelade 238020 saturne
uranus 2876679082 soleil
miranda 129900 uranus
ariel 190900 uranus
umbriel 266000 uranus
titania 435910 uranus
obéron 583520 uranus
neptune 4503443661 soleil
triton 354759 neptune
néréide 5513400 neptune
larissa 73548 neptune
\.
--
-- Name: ss_object_pkey; Type: CONSTRAINT; Schema: test; Owner: -; Tablespace:
--
ALTER TABLE ONLY ss_object
ADD CONSTRAINT ss_object_pkey PRIMARY KEY (name);
--
-- Name: ss_object_is_satellite_of_fkey; Type: FK CONSTRAINT; Schema: test; Owner: -
--
ALTER TABLE ONLY ss_object
ADD CONSTRAINT ss_object_is_satellite_of_fkey FOREIGN KEY (is_satellite_of) REFERENCES ss_object(name);
--
-- PostgreSQL database dump complete
--
Raw
SsObjectMap.php
<?php
namespace YourDb\PublicSchema;
use YourDb\PublicSchema\Base\SsObjectMap as BaseSsObjectMap;
use YourDb\PublicSchema\SsObject;
use \Pomm\Exception\Exception;
use \Pomm\Query\Where;
class SsObjectMap extends BaseSsObjectMap
{
public function getSatellitesOf($name)
{
$sql = <<<_
SELECT
:select_fields_satellite_alias_planet,
array_agg(satellite) AS satellites
FROM
:satellite_table planet
LEFT JOIN :satellite_table satellite ON
planet.name = satellite.is_satellite_of
WHERE
planet.is_satellite_of = $*
GROUP BY
:group_by_fields_satellite_as_planet
ORDER BY
planet.distance ASC
_;
$sql = strtr($sql, array(
':select_fields_satellite_alias_planet' => $this->formatFieldsWithAlias('getSelectFields', 'planet'),
':satellite_table' => $this->getTableName(),
':group_by_fields_satellite_as_planet' => $this->formatFields('getGroupByFields', 'planet')
));
$this->addVirtualField('satellites', sprintf('%s[]', $this->getTableName()));
return $this->query($sql, array($name));
}
}
Raw
structure_output.txt
\d ss_object
Table « public.ss_object »
Colonne | Type | Modificateurs
-----------------+-------------------+---------------
name | character varying | non NULL
distance | bigint |
is_satellite_of | character varying |
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment