Re: [web-devel] Join support in persistent

[snip]

I think you're solving a different problem. Are you talking about the

...

fact that the EntryAuthorIn constructor takes a list instead of a Set? That's not where the slowdown comes from. Actually, for the current backends, a set would needlessly slow things down, since the In constructor simply converts things to SQL and lets the database do the work.

I'm not sure what you're suggesting here to be honest, can you clarify?

An O(m + n) implementation instead of O(m * n) by using constant lookups instead of repeatedly searching through a list.

But where will you use the Set? The slowdown is because we end up with two

On Sun, Apr 3, 2011 at 9:40 AM, Greg Weber wrote: lists: [(Key one, one)] and [(Key many, many)]. For each (Key one, one), we need to filter the entire [(Key many, many)] to find the records which match each one. As far as the code is concerned, doing the initial filter to the relevant (Key many) values is constant*. * Well, it's "constant" in the sense that the database backend handles it. In the case of SQL, it's converted to a "Entry"."author" IN [1,2,3]. How the database handles that code is outside the scope of this optimization.

[snip]

...

...

It would be best for it to also be clear from the function names or arguments..

I actually thought it *was* clear that it would be an inner join and not an outer join. But how would you change the names? I don't want to end up with selectJoiningOneToManyRelationshipUsingInnerJoin ;)

How do I know it is an inner join? If there is just one function I would assume it is an outer and I can get an inner by filtering the results, not that there is no way to do an outer. If there is a second function that is called *Outer, then it might be clear that the other is an inner. The other approach is instead of EntryAuthorEq to have something like InnerJoin (if possible) or otherwise EntryAuthorInner.

[snip]

...

...

...

I agree, I am not trying to say that we need to elegantly handle every possible query. I am just pushing that for those that we are currently handling to be elegant. Persistent integration with directly writing SQL should probably be a high priority.

Can I get some feedback on what's missing for this? In the Database.Persist.GenericSql.Raw module[1], there are two functions (withStmt and execute) that let you run any SQL command against the DB you want. I've used this myself when I needed to do something that Persistent didn't allow (a full text search in my case).

I know that the functions are neither pretty nor well documented, but what's missing that is preventing people from dropping down to SQL now? If it's just a documentation issue, I'll address it.

Michael

Yes we need docs. Is it possible after a raw query to build the data structures back up? - achieve the same return result of selectList? The other aspect of integration would be the ability to mix SQL fragments with a normal persistent query. For example, for the sql join function under discussion the user could supply the raw join SQL to make it an outer join.

I think by exposing (and documenting) some of the functions that Persistent uses internally, we can get pretty far here. For example, for marshaling, the most useful function is likely fromPersistValues[1], which anyone can use right now. However, the user needs to be careful to pass in the right set of fields. Michael [1] http://hackage.haskell.org/packages/archive/persistent/0.4.2/doc/html/Databa...