Sorry about the long email first!
Using Hibernate 3:
This is a more general question rather than just Hibernate, I am posting here as I don't know where else to ask, just looking for some advice from expert database query experts.
I have a situation where I am modifying an application that has a very generic data model such as:

An issue table { issue_id,description,resolution,user_id,etc...}
An issue_fields table { id,field_id,field_value,issue_id }

There is a separate table that manages field/field_ids... about their types/names...
The application lets me create an issue_field and assign it to an issue. So, there are a number of fields assigned to each issue. An example data looks like this:
issue:
issue_id desc resol user_id
10 xxx yyyy 345
11 zzz www 234

issue_fields:
id field_id field_value issue_id
1 101 camera 10
2 201 film 10
3 101 phone 11
4 201 battery 11

So, my problem is I would like to search for all the issues that has field_id of 101 && field_value of camera and field_id of 201 and field_value of film. It only seems possibe to obtain all the issue/issue_fields using a join and then applying these conditions in memory. But this is not a good solution as I have thousands of records and I am not able to paginate the records as I have to bring all the records in memory in order to apply the conditions.

Is it possible to write such a query here that will let me apply these conditions at the query/database level?

Thank you very much.

Yes, it is not very complicated. You have to join the issue_fields table one time for each field you want to query.

Eq. something like this:

Thanks nordborg:

I have another scenario here for Pagination. In this case I have a parent child relation ship in a separate table: because of many-to-many relationship. All the vendor entries are in a table: such as:
Vendor: id, name, etc..
Relatinship: id, par_id, child_id

I would like to paginate when a user searches for a name/part of a name, so that all the matching vendors are displayed who has that string in their name however all the child vendors are not displayed. So, if searched for a string 'a_ge' and if 'a_ge' is a child of 'ge' then the parent vendor 'ge' is displayed only not the child vendor 'a_ge'.
Is it possible to for a sql query for a situation like this which supports pagination?

Regards,
Olonga.

In this case using joins may produce duplicates of the vendor if the filter matches more than one child. It is possible to remove the duplicates by using the distinct keyword. Eg.



Annother possibility is to use subselects:

Thanks again.
I have all the vendor info in one table, so I replaced 'Child' by 'Vendor' again in your sql.
So, even though this works great, there are cases when some vendors do not have a parent or any children. Is it possible to inject that condition into this sql so that one sql can bring out just the parent vendors, those with some child vendors or no child? I have used the following sql just to extract such vendors:

select distinct p.id, p.name, r.child_id from vendor p left join relationship r on r.child_id = p.id where r.child_id is null and p.name like '%eg%'

Also, if a child is a parent to another vendor, this sql will still display that child.

Oh, I didn't realize that the Vendor was both a parent and child. This and the case with no parents/children makes things a bit harder, though I still think it is possible. But I don't think I am able to get this kind of SQL correct without actually trying it on some real data and a hand-made list of the expected results to verify against.

The way forward is probably with left joins and/or subselects.

The solution was much easier than I thought:

select distinct p.id,p.anme,r.par_id,r.child_id from vendor p left join relationship r on r.child_id = p.id where r.par_id is null and lower(p.name) like '%eg%'

Basically just gets all the vendors along with those which are a child to some party.... now in that list if the par_id is null then it means that vendor is either a parent to some other vendors or it does not have any parent/child rel.... That's it...