Presentation (drafting)

Notes for public talks.

ORM, Query Builder and Raw query writers. Can we be friends?

• all of us have our patterns.
• some patterns are generally accepted.
• one place in our application seems to have some indecisiveness.
• raise of hands - prefer ORM, Query Writers and RAW queries.

How can we become friends

1. Learn each-others pains
2. Learn each-other benefits
3. Create a proof of concept.

• Query Builder that can build ANY query.
• ORM that can build any query

4. Question why ORM is needed?
5. Design a good syntax that resonates with PHP language.

Time to reinvent the wheel

• The realisation. We must understand that none of those solutions are great.
• I will tell you about my research, the prototype and current status of things.

Raw Query Writers

What can we learn from you:

• control, be able to decide
• learning SQL
• any-query on stack overflow

Signing up to be friends:

• 70% - query constructed for you by framework in a best way
  • avoid unnecessary parts
  • minimise data traffic
  • use full capabilities of the database
• 15% - you can use raw query as a part of the automated query
• 10% - you can use automated query as a part of raw query
• 5% - write entire query yourself

Hey, guys, we understand and welcome you.

ORM

Reasons to use:

• good theoretical concept
• database refactoring
• big developer teams
• vendor transparency
• fast development time
• low TCO

Signing up friends:

• 20% ability to use CRUD with SQL and NoSQL db vendor
• 15% impedance mismatch, it's when database model does not match object-oriented entity model
• inheritance and multi-table entities
• solve N+1 and lazy/eager loading problem
• solve aggregate fields
• behaviours
• extensions
  • soft delete
  • audit
  • sortability
• automated table / field aliasing
• deal with parameters and escaping

Query Builders

Query Building is a way to go! But building queries is hard. Data Mapper to build them for me yet let me tweak them.

• model - persistence - query builder - all independent, but work in tandem.
• persistence maps parts of model into expressions
• persistence maps actions into different query types

Demo

• quick raw query: select (select concat(name,' ',surname) full_name from author where author.id = book.author_id and deleted=0) author, title from book
• hey it's better to build it: $db->dsql()->table('book')->field($db->dsql().. (composable query builder)
• lets have someone else build that stuff for us
• define two models, ask them to build query
• author mapped into query then was used as a field
• now we can abstract and simplify things
• tweak our final query - create group by for a report.

More Examples

• Entity to Query Builder (report)
• RAW Expression into Field (full_name)
• Query Builder into Field (sub-select)
• Field into Expression (condition in sub-select)
• Individual Field Query (author name)
• Custom Condition (name is null or surname is null)
• Condition on expression ( addCondition(full_name) )
• Conditions between fields ( name = surname )
• Entity to Join multiple tables ( join on book.info_id = book_info.id )
• Forward and Reverse join ( join book.id = book_info.book_id )
• Single table inheritance ( BookWithInfo extends Book )
• Class table inheritance ( Book extends Publication, Magazine extends Publication )
• Inheritance Mapping Override ( Magazine.table = magazine )
• Multiple types ( eBook extends Book )
• Protected joined Table ( Book->joinReadOnly(Author) )
• Calculated Aggregate Fields ( Author.totalBooks = sum(ref(Book)->count)
• Relation-based expression ( Book.author_name = ref(author_id)->field(name) )
• Proper sub-query conditioning ( Author.totalMagazines = sum(ref(Magazine)->count) )
• Field re-naming ( Book->hasOne('author_id', 'Author')->addField('author', 'full_name');
• Field splitting ( full_name -> name / surname )
• Custom field types (
• Deleting fields ( eBook getElement('issn')->destroy(); )
• Aliasing ( magazine->ref('previous_issue')->addField('name') )
• Specific fields ( book->onlyFields(..) )
• Type mapping ( )
• Field Value Expressions
• isolated OR, nested conditions ( where .. and (.. or .. ) .. )
• default for conditions (author->ref(book)->insert('new book'));
• enforcing conditions ($book->addCondition('year', 1995)->insert(['new book', 'year'=>1993]) )
• enforcing expression conditions ($book->addCondition('author_id', $book->ref('Author')->addCondition('gender','F')->action('field', ['id']));
• traversal without loading( $book->withID(123)->ref('Author')->loadAny() )
• deep traversal ( $author->ref('Book')->ref('Comment')->ref('poster_id') )
• traversal with scope ( $author->ref('Book')->ref('Comment')->addCondition('rate', 5)->ref('poster_id') )
• system-wide conditions ( new Book($db); // conditions applied from $app scope! )
• support for soft delete
• different soft-delete strategy ( $book->delete() / $book->softDelete() )
• undelete
• dirty fields ( $book->set('title', 'oops'); $book->unset('title'); )
• reverting edits ( $book->ref('Audit/Revisions')->loadAny()->undo() );