Login Skip Navigation LinksWilsonORMapper > Forums Search
Demo Version Demo Version
Download and try for yourself a fully working demo version, including sample apps and documentation.  The only limitation is that the demo version only works inside the debugger.

PayPal Subscribe
Get It All for $50 USD:
WebPortal, ORMapper,
Source Code, All Updates
PayPal

User Login User Login
Log In
 
 
Reset Password

Wilson ORMapper Forums Wilson ORMapper Forums : OPath Queries : TOP + LEFT JOIN + ORDER

Date Post
9/6/2006 2:12:32 PM

Hi Friends,

Here is one example how I have migrated this regular sql statement to ORMapper:
//SELECT TOP 1 * FROM page_Content  WHERE PageId=@PageId

string where = "PageId=" + PageId;

ObjectQuery<PageContent> query = new ObjectQuery<PageContent>(where, string.Empty, 1, 1);                       
ObjectReader reader = DataManager.ObjectSpace.GetObjectReader(query);

Ok, just work fine :p

Here is one more complicated that I want to migrate :

//SELECT TOP 1 * FROM page_Content
//LEFT JOIN page_Attachments on page_Content.FileAttachId=page_Attachments.FileAttachId
//WHERE PageId=@PageId ORDER BY PageVersion DESC


I need some help how I can do this to pass to the next similar queries and avoid to ask here how to do it :P.

If I didnt have the "TOP 1" sql statement I think I could produce this using the OPathQuery
I would also need to create one "Naming Relationships for OPath".

This is what I can do BUT is wrong !!! This is very dificult to me :(

<relation relationship="OneToMany" member="PageAttachObject" field="FileAttachId"
 type="Entity.PageContent" alias="Attach" lazyLoad="true" />

string where = "PageId=" + PageId;
OPathQuery<PageContent>(null, where ,"PageVersion")

I can’t figure out how to do it... please, can you put here how you do it ?

Thanks
9/8/2006 8:00:04 AM I would appreciate some help here, thanks
9/12/2006 8:09:17 AM

I'm not 100% clear on what what you are trying to do, but it looks like you want to get records in the page_Attachment table based on a key in the related page_Content table.  If that is correct, you'll need to add a relationship from the attachment table to the content table.  Like one of the following:

<relation relationship="ManyToOne" alias="PageContent" type="PageContent" field=" FileAttachId" member="_pageContentHolder" lazyLoad="true" />

<relation relationship="ManyToOne" alias="PageContent" type="PageContent" field=" FileAttachId" queryOnly="true" />

Then you can write a query that will traverse the relation to filter by page id:

OPathQuery<PageAttachment>("PageContent.PageId = ?", "PageVersion ASC")

This will return all the PageAttachment records that have a related PageContent record with the specified page id.

Hope this helps,
Jeff Lanning
9/12/2006 8:45:42 PM

Hi Jeff,

Thanks for trying to help me. I am new in the WilsonORMapper tool and I am trying hard to learn.

Your answer is more or less what I am trying to get. Thanks!!

There is in the page_Content table several pages with the same pageId... the only difference is in each PageVersion they have (we can have several pages with same pageID with different PageVersion). The idea here is to get the Last Version (PageVersion) of that page (pageId) with all the fields related.

Because of that I only need one row (SELECT TOP 1 * FROM page_Content…… ORDER BY PageVersion DESC)

The (JOIN page_Attachments on page_Content.FileAttachId=page_Attachments.FileAttachId) is necessary because I need to get the extra fields (all fields) from the page_Attachments that give me the extra information that I need. I get those extra fields using the FileAttachId key.

In the end I will get one row with the last page version (all fields of page_Content) and all fields from page_Attachments table based on the key all fields FileAttachId.

This query is no easy to do because I need to put the (TOP 1) and the (LEFT JOIN) in the same query !! In one SQL query statement is very easy to do it but using the ORMapper is very complicated !!! Is that what I am trying to find the solution !!

Yes, you have revealed me some important notes that I must to learn to do it but are not complete. I need to reproduce this query in the ORMapper:

//SELECT TOP 1 * FROM page_Content
//LEFT JOIN page_Attachments on page_Content.FileAttachId=page_Attachments.FileAttachId
//WHERE PageId=@PageId ORDER BY PageVersion DESC

I am very near to get the solution. All help will be great !!
I have lots of queries to reproduce and all of them are very similarly to this one.

Note: I have to wait 6 days to get one answer. Please, people, help the new members :p
Thanks
9/17/2006 9:41:42 AM

TOP is not supported directly in the OPath language.  The best you could do and still use an OPathQuery would be to build a expression to get all versions for the page in desc order and execute it with GetObject.  This will get you the top object returned. SQL still has to build the results though, but WORM will only process the first record and return it.  This may or may not be acceptable in your scenario.

 

Another option is to use an ObjectQuery and the QueryHelper instead, which will let you build a SQL query manually.  There should be no problem including TOP 1 in this case.

 

NOTE: You only had to wait 5 days this time.

 

- Jeff Lanning
9/18/2006 1:34:54 PM

Hi Jeff,

Thanks for the reply :)

>>Another option is to use an ObjectQuery and the QueryHelper instead, which will let you build a SQL query manually.
>>There should be no problem including TOP 1 in this case.

I am trying to avoid that "build a SQL query manually" ! I want that my web application work with several databases (SQL Server,MySQL,PostgreSQL, Access)
If I include "TOP 1" this would not work in the MySQL. The MySQL database dont have "TOP" !

Ex:SELECT TOP 1 * FROM [MyTable], becomes SELECT * FROM [MyTable] LIMIT 1

Thanks again for the tips
9/18/2006 2:12:18 PM The ObjectQuery allows you to specify page size and count in a way that the ORMapper will build the sql for the correct database.  In this case case you want the first page with the size also being one.  I hate to be firm, but I've also given you the correct syntax for doing this in your other thread several weeks ago, so I'm assuming I do not need to do so again.

Note that using the ObjectQuery is not somehow "worse" than using OPath -- it simply expects you to think using sql syntax instead of object syntax.  Some people prefer one technique over the other, so both are included, but one is not "worse" or "better" than the other.  You use the QueryHelper with the ObjectQuery so that you won't be manually building expressions and worrying about table and field names, so it basically lets you think in sql syntax while still helping you with the actual sql.  It is true that Jeff has supplied a lot more operators in the OPath syntax, but they don't apply to most queries, and in most cases they are the types of things that are pretty standard in most databases anyhow, so I still think its almost always a case of your preference being sql or object syntax.  I'm an OO developer, but I'm also a database developer, and I like to think of my queries in terms of sql because of that -- no matter how much O/R Mappers bridge the gap its still true that you are working with a database in the end, and I already know sql so I don't see the point in learning another language (like OPath).  That's also the beauty that MS is working on for Linq -- you get to work with sql-like syntax instead of learning a new language, although certainly the tool support will make it better than what I can offer.

Anyhow, to make a long story short, you do have what you need to create this query, and even though it may not be possible with OPath, all that means is that you have to think in terms of sql -- it does not mean that you actually have to create all the sql yourself or get stuck without support for multiple databases.

Thanks, Paul Wilson
9/19/2006 10:03:48 AM

Hi Paul,

>> I hate to be firm, but I've also given you the correct syntax for doing this in your other thread several weeks ago
No problem! Note that I am not asking the same question. I only said that I am trying to avoid that "build a SQL query manually" and give the example, why :p

>>You use the QueryHelper with the ObjectQuery so that you won't be manually building expressions and worrying about table and field names, so it basically lets you think in sql syntax while still helping you with the actual sql.
>>...Anyhow, to make a long story short, you do have what you need to create this query...

I did not say that I don’t have what I need to create this query... the problem is HOW to create it !
Please provide examples !!

If I want this:

//SELECT TOP 1 * FROM page_Content
//WHERE PageId=@PageId

I do this:

int PageId = 1;
string where = "PageId=" + PageId;
ObjectQuery<PageContent> query = new ObjectQuery<PageContent>(where, string.Empty, 1, 1);   

Can you put the sample using the QueryHelper/ObjectQuery to build this query ? :

//SELECT TOP 1 * FROM page_Content
//LEFT JOIN page_Attachments on page_Content.FileAttachId=page_Attachments.FileAttachId
//WHERE PageId=@PageId ORDER BY PageVersion DESC

That is my question!! How to do it!!

Sorry and please understand that I new in this kind of stuff and I need some "basic" help.!
Thanks again :p
9/19/2006 2:56:11 PM Its a minor difference to use the QueryHelper:

int PageId = 1;
string where = QueryHelper.GetExpression("PageContent.PageId", PageId);
ObjectQuery<PageContent> query = new ObjectQuery<PageContent>(where, string.Empty, 1, 1);

What's the difference?  It will take care of the things like quotes for strings, including "cleaning" of strings, and things like formatting of dates for your specific database.  It also means that you can think in terms of your Class.Property naming, so somewhat OO, without having to worry about table and field names which is another good abstraction.

Again all I can say is this is my personal preference, but I don't want to imply OPath is not just as "good" -- its simply a case here with paging where OPath doesn't yet give you what you need.

Thanks, Paul Wilson
9/19/2006 3:42:15 PM

Hi Paul,

Thanks for the reply and for your professional opinion ;p

By the why… my question remains… using the same approach (QueryHelper/ObjectQuery to build queries) How you do this ?:

//SELECT TOP 1 * FROM page_Content
//LEFT JOIN page_Attachments on page_Content.FileAttachId=page_Attachments.FileAttachId
//WHERE PageId=@PageId ORDER BY PageVersion DESC

The problem here is how you reproduce the LEFT JOIN using the QueryHelper/ObjectQuery approach

Thanks

 
9/19/2006 5:15:59 PM Have you looked at the lookup feature?  The Example app has an example, and it is documented in the Wiki with all the other mapping docs.  Without knowing a lot more about your particular case I can't give you the exact details, but it sounds like this would be what you want.  If that isn't it for some reason, then I typically recommend creating a view -- not totally database independent, but its almost universal.

Thanks, Paul Wilson