Well, complex to me at least.

CREATE TABLE reports ( 
   report_id serial, 
... 
);
CREATE TABLE similar ( 
   similar_id serial, 
   backup text,
...
);
CREATE TABLE choices ( 
   choice_id serial, --PK
   report_id int NOT NULL, --FK
   similar_id int NOT NULL, --FK
   chosen boolean,
   chosen_time timestamp NOT NULL,
...
);

A process goes through the reports, bundles them into similar groups with choice entries where chosen is null. A series of users make choices of the one in the group to keep, setting chosen to true. The last choice wins.

This is the Postgres 8.3 friendly query that we came up with to do this in one query.

SELECT reports.* FROM 
 (SELECT choices.* FROM similar
  JOIN choices ON similar.similar_id=choices.similar_id
  WHERE similar.backup='' AND choices.chosen IS NOT NULL) AS desired_choices
 LEFT OUTER JOIN 
  (SELECT choices.* FROM similar
  JOIN choices ON similar.similar_id=choices.similar_id
  WHERE similar.backup='' AND choices.chosen IS NOT NULL) AS greater_choices
 ON greater_choices.report_id = desired_choices.report_id
 AND greater_choices.chosen_time > desired_choices.chosen_time
 JOIN reports on desired_choices.report_id = reports.report_id
 WHERE greater_choices.chosen_time IS NULL --indicates this is the newest
 AND desired_choices.chosen = true;

I'm really not sure where to even start to replicate it in LLBLGen. Also, this is part of a querying system so other predicates will be tacked on later. Do I need to resign myself to a query for report_ids to use as a FieldCompareSetPredicate?

Currently using 4.0, but I could probably upgrade to 4.2 if need be.

Are you familiar with Linq? Or our own query system 'Queryspec' ? Do you use adapter or selfservicing?

I am familiar with Linq and use it extensively elsewhere, but this is for a tool providing optional filtering/paging/sorting to customers, and I've had lots of pain with trying to make that work with Linq.

I have not investigated Queryspec and I am using Adapter.

Did you try using Derived tables and dynamic relations?

Also check the example queries in the installation folder\Frameworks\LLBLGen Pro\ExampleQueries folder. There are many queries for e.g. queryspec (which have their linq equivalent mentioned as comments with them) which contain queries on derived tables/nested queries, so you should be up and running in no time.

Unfortunately, time waits for no man, and I'd already implemented it with a 2 fetch solution using raw sql to retrieve ids before Otis replied the first time. At least now I have a target for improving performance in the future .

QuerySpec looks very interesting but would involve rewriting lots of predicates and sort expressions already built into the application. Wish I had time to switch, because there's something about the predicate system that always makes me feel like a noob when I have to do anything beyond a simple query. I will give it a whirl next time I need a new independent query for something.

I've read through that Derived Table page a couple of times and see a faint glimmer of hope that I could use it instead of my sad raw sql. Searching around, I don't see anything examples of joining two derived tables to each other and to the target table. Could you give me a pseudo sketch of how I would build this up?

tmatelich wrote:

Unfortunately, time waits for no man, and I'd already implemented it with a 2 fetch solution using raw sql to retrieve ids before Otis replied the first time. At least now I have a target for improving performance in the future .

QuerySpec looks very interesting but would involve rewriting lots of predicates and sort expressions already built into the application.

No that's not required Queryspec accepts predicates and sort expressions so it's not needed to rewrite those if you already have methods producing them.

Wish I had time to switch, because there's something about the predicate system that always makes me feel like a noob when I have to do anything beyond a simple query. I will give it a whirl next time I need a new independent query for something.

The low level system is not that easy, I know . The elements coming with queryspec can help with that too though. E.g. (field==value).And(field2==value2), or field.Ascending(), to produce a sortclause; there are many extension methods added by queryspec which produce known elements like predicates or sortclauses you can use in your queries.

I've read through that Derived Table page a couple of times and see a faint glimmer of hope that I could use it instead of my sad raw sql. Searching around, I don't see anything examples of joining two derived tables to each other and to the target table. Could you give me a pseudo sketch of how I would build this up?

First you construct the derived tables. Then you join them together using DynamicRelation objects. A dynamic relation can accept 2 derived tables, and a predicate.

This will result in: derivedTableA JOIN derivedTableB ON predicate