R::findAlike functionality

[marcioAlmada]

Intent

This issue intends to discuss a PROPOSITION to add a new group of methods to R facade:

• R::findAlike()
• R::findFirstAlike() || R::findOneAlike()
• R::trashAlike() (maybe?)

Motivation

Sometimes @gabordemooij defines Redbean as "A bridge between objects and records". Find alike methods are just one more step into this direction.

This proposition has no intention of suppressing Rx or #311. It can coexist or even depend on these implementations.

Usage

These new methods would behave like a QBE (query by example) tool, receiveing bean prototypes to configure database searches. Example 1:

Let's imagine an hypothetical application with a search form with lot's of fields. Every filled form field would create a new condition for the application to find information. With R::findAlike, userland code would look like this:

$prototype = R::dispense('project');
$prototype->import( $this->request->post->search );

$projects = R::findAlike($prototype);

Without R::findAlike, userland code would potentially look like this:

$conditions = [];
foreach($this->request->post->search as $field => $value)
{
    $conditions[] = "{$field} = ? ";
}

R::$f->begin()
    ->select('*')->from('project')
    ->where($conditions)->put( $this->request->post->search )->get();

Also, sometimes you already have a loaded bean and want others that are alike. Instead of creating some voodoo to conver that bean into a new search we would simply... Example2:

$projects = R::findAlike($cool_project);

Advantages

• You can keep thinking about objects
• It's more programmatic than Rx fluid syntax or any other tool provided at moment

Disvantages

• It does not offers a way to deal with relationships, but I would not consider it a problem
  since more complex db interaction should fall back to SQL usage.

[zewa666]

Answer to #309 (comment)

I cannot second guess what another user may would like to do with it but have you every worked with Access or Excel? Those are utilizing Microsoft QBE http://en.wikipedia.org/wiki/Microsoft_Query_by_Example
So since there are pretty lot people using Access, there must be a potential use case for it right :)

My personal use case for it is because I'm creating QBE filters for my users. Why ... well I'm too lazy to create big filter forms with tons of logic to catch every potential filter combination. Secondly my customers do not all understand SQL, so they are not capable to get the information natively. Using QBE as visual guideline already prepares the data, and having a QBE filter approach in RB would simply making programming it easier.

[marcioAlmada]

@zewa666 updated main post with your example and also the QBE stuff :)

[gabordemooij]

Okay, so I have a project and I would like to query similar projects..

 $similar = R::findAlike($project);

Of course we do not want a project with the same ID. But now the question arises,
do we want only projects that are 100% similar? Or do we want to compare certain properties only?

 $similar = R::findAlike($project, ['name', 'start']);

Also what kind of comparison do we want? Just == or also projects that started later?

$similar = R::findAlike($project, ['name', 'start'=>'>=']);

What about certain parts of the name, AND-OR combinations?

 $similar = R::findAlike($project, [ ['name'=>[ '%project'... ].... ]); 

To me SQL seems way simpler and even more powerful:

 $similar = R::findAlike($project, ' name = ? AND start >= ? ', [$name, $start]);

Early version of RedBeanPHP had exactly this. R::find used to work like R::findAlike, my point is that
in the end R::find() became unmaintainable and messy. I then ripped out all code behind R::find and replaced it with simple SQL.

For more details on problems regarding QBE (inspiration for RedBeanPHP comes from this article):
blogs.tedneward.com/2006/06/26/The+Vietnam+Of+Computer+Science.aspxIn

Also, as I said we can't write SQL inside R::find() because this SQL has to be written in the Query Writers - foreach database! This is a lot of work and each additional method in the writers makes RB less portable.

However, given the R::find() method we can take some steps closer to a QBE, what would you think of:

 R::find($project, ' name = :name AND started = :started ');

Because we have no bindings parameter we decide to scan the query string and replace the named slots with their corresponding values in the prototype bean.

cheers,
Gabor

[zewa666]

Guess QBE is not such a typical word nowadays as it used to be :)
First of all QBE not only is the idea how to query, but also delivers a standard how queries a build. It was created by Moshee Zloof an IBM engineer same time as SQL was created so kinda a complete standard.

Here is a nice research about QBE itself http://pages.cs.wisc.edu/~dbbook/openAccess/thirdEdition/qbe.pdf.

In short some principles:

• only set properties are used for filtering (users in general do not search for NULL)
• if only values are provided you use equals (=) operator
• values may contain OR/AND and even LIKE or % to indicate partial queries
• values may contain directives like ">"; ">="; "<>"; "<="; "<";

So besides that to help users querying easier some implementations contain help words like "BETWEEN x AND y" instead of adding two conditions like "> 2013-01-01" and "< 2013-12-12"

Phpmyadmin is one of the rare tools leveraging a pretty nice QBE functionallity, maybe take a quick look at this Post explaining nicely how to work with it.
http://www.packtpub.com/article/multi-table-query-generator-using-phpmyadmin-mysql

[marcioAlmada]

@gabordemooij R::findAlike would always skip primary key, finding a bean that has the same id as the prototype don't make sense.

These use case ramifications you pointed out are the same I pointed for Rx and the main reason I started #311, by the way.

Simple SQL seems more powerful because you're ignoring one factor of the example scenario: dealing with dynamic conditions. Writing SQL will always lead to messed string manipulation in userland code, like in example 1.

@zewa666 how about a more detailed example? Mine is too simple.

[gabordemooij]

@marcioAlmada you mean the $conditions variable.
Okay I see your point.
Agreed, this would be useful addition.
I took some time for me realize the practical usage scenarios.
The $conditions variable could be linked to a form - no longer requiring the user to build the query manually.

However there is still an implementation concern: how do we keep the QBE from writing its own specific SQL? Do we have to all QBE methods to the writers or can we find a more elegant solution? Do you have a plan to approach this practical challenge?

[marcioAlmada]

I have no idea. Maybe such functionality like R::findAlike could be much easier to implement on top of some helper like the one described in #311 or Rx.

[gabordemooij]

I think this functionality would work best as a plugin, that way we can build this plugin for MySQL-like databases only without having to port all functions to Postgres, CUBRID, SQLite and Oracle (and MSSQL/DB2/Firebird in the future).