This is an automated email from the git hooks/post-receive script. New commit to branch feature/spgeed in repository pollen. See https://gitlab.nuiton.org/chorem/pollen.git commit 3b442887f2cc92f0e59c9358f823e57b6db91339 Author: Killian <killian.herbreteau@epitech.eu> Date: Wed Nov 13 14:31:33 2019 +0100 spgeed doc : adding with desc --- spgeedDAO.md | 79 ++++++++++++++++++++++++++++++++++++++++++++++++++---------- 1 file changed, 66 insertions(+), 13 deletions(-) diff --git a/spgeedDAO.md b/spgeedDAO.md index 6acf9ae8..7c188e7d 100644 --- a/spgeedDAO.md +++ b/spgeedDAO.md @@ -1,13 +1,7 @@ -# How To +# Create Spgeed Dao -This `How To` will describe you how to reproduce / continue some work done / To Do on Pollen. -* Create Spgeed Dao - -## Create Spgeed Dao - -`Spgeed Dao` must to be written with Strings - -Example : +There was 2 ways to create `Spgeed Dao` : +- Using Spgeed annotation ``` java public interface PollenUserSpgeedDao { String getUsers = "SELECT * FROM pollenuser"; @@ -16,12 +10,24 @@ public interface PollenUserSpgeedDao { PollenUser[] getUsers(); } ``` +- Implementing SpgeedDao +``` java +public interface PollenUserSpgeedDao implements SpgeedDao { -### Simple request + public PollenUser[] getUsers() { + Map args = new HashMap(); + Query query = new Query(getSession(), "SELECT * FROM pollenuser, map, PollenUser[].class); + + return (pollenUser[]) query.executeQuery(); + } +} +``` +We decided to use `Spgeed Annotation` to avoid overwriting line and adding complexity to Spgeed Dao to keep them easy to read. +## Simple request !!! warning - sql String can't be created using method - + sql String for annotation can't be created using method + Bad Example : ``` java public class userDaoUtils { @@ -62,7 +68,54 @@ public interface PollenUserSpgeedDao { } ``` -### Dao Structure +## Joining table +To retrieve object from database we have to connect multiple table. + +For the example PollenUser have a default emailaddress and multiple emailaddresses (reference to PollenUserEmailAddress). +``` java +PollenUser user = /* getUser */ +user.getEmailAddresses(); // EmailAddresses +user.getDefaultEmailAddress(); // DefaultEmailAddress +``` +### Using WITH +Before joining table we have to retrieve all the data of every table to join. +Example for user where we get every date from `pollenUserEmailAddress` : +``` sql +WITH emails_agg AS ( + SELECT e.topiaId, e.emailAddress, e.pollenuser + FROM pollenuseremailaddress e +); +``` +### Join +After getting `emails_agg` filled with `pollenUserEmailAddress` table we have to join those tables. + +Every pollen join have to be `LEFT JOIN`. + +Retrieving date: +* One to one `1.1` link : +``` sql +json_agg(demail.*)->0 AS defaultEmailAddress +``` +* One to many `1.*` link : +``` sql +COALESCE(json_agg(emails.*) FILTER (WHERE emails.pollenUser IS NOT NULL), null) AS emailAddresses +``` +Here's a complete request of pollenUser joined by pollenUserEmailAddress: +``` sql +WITH emails_agg AS ( + SELECT e.topiaId, e.emailAddress, e.pollenuser + FROM pollenuseremailaddress e +), users_agg AS ( + SELECT pu.topiaid, pu.name, + json_agg(demail.*)->0 AS defaultEmailAddress, + COALESCE(json_agg(emails.*) FILTER (WHERE emails.pollenUser IS NOT NULL), null) AS emailAddresses + FROM pollenuser pu + LEFT JOIN emails_agg demail ON demail.topiaid = pu.defaultEmailAddress + LEFT JOIN emails_agg emails ON emails.pollenUser = pu.topiaid + GROUP BY pu.topiaid +) SELECT * FROM users_agg; +``` +## Dao Structure How to structure dao : ``` java -- To stop receiving notification emails like this one, please contact chorem.org SCM administrator <admin+scm@chorem.org>.