This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
Hi, we have tables tickets and ticketbets. Ticketbets have PicksValue column. TicketBets belongs to tickets (tickets can have many ticketbets)
We need to 1. select all tickets that have 3 ticketbets on them. 2. select all tickets that have sum of ticketbets.PickValue > 30 3. Select just Tickets.TicketBets.PickValue column and Tickets.DateTime column This 3 things are (for now) only 3 scenarios that we couldn't get with lightspeed. It would be great if you can help us to get it to work. |
|
|
Please note that i have try to run this:
Tickets.Name column is varchar type in sqlite And i get this SELECT |
|
|
I'm looking into queries 1 & 2. For the projection you just need to call Project instead of Find. Cheers, Andrew. |
|
|
Thanks Andrew, this one with Project instead of find do the job :) I'm waiting for examples of queries. Regards, Haris |
|
|
Also is there a way to write cusotm SQL Query - query that will use already opened connection from LightSpeed and execute our sql? |
|
|
Hi Haris, You can use the PrepareCommand method on IUnitOfWork for this purpose. It returns an IDBCommand object which you can recast back to the database specific command if required e.g. command = UnitOfWork.PrepareCommand(command) as SqlCommand;
|
|
|
Thnx Jeremy, that do the job, but still i'm waiting for answers 1 and 2 in my first post on this thread. If we complete all of this with your ORM then we will try one more test and if it succeed the we will for sure buy license for it. Regards, Haris |
|
|
Any help here :) ? |
|
|
Hi, Sorry for the delay. Unfortunately, we don't yet support the SQL HAVING clause required for those queries. One work-around (and one I use quite often for performance) is to denormalize the count and sum values into columns on the parent table. This approach is fast but obviously requires keeping the columns up to date. It is not uncommon however. E.g. In Rails it's called a counter cache. If this approach is acceptable, we could look at supporting it declaratively in LightSpeed. Cheers, Andrew. |
|
|
Hi, Andrew What can be good way to go is something like this, Column Count (Which holds number of children rows, and on insert/update/delete of child row, you update count column in parent table - this is how counter cache works in Rails). Column ParentSumColumnName (something that works like Value Objects right now in lightspeed) on place of Sum can go Max or some other aggregate function, and column name is name of the column on which you run previously defined function. Again this column is updated on insert update delete of child rows. What do you think ?
|
|
|
Exactly, counter cache is a feature we'd definitely look to add to LightSpeed itself. We would make it like the Rails one. i.e. When a new child is saved generate an UPDATE statement for the counter in the parent table. Cheers, Andrew. |
|