cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "John" <mmmmmmmm...@web.de>
Subject Re: Select Query on Table with no Primary Key
Date Thu, 07 Jun 2007 15:41:51 GMT
opps forgot a part from the SQL

SELECT e.* , (SELECT COUNT(*) FROM MyView e2 WHERE e2.name <= e.name ) AS 
rownumber
FROM MyView e
ORDER BY name


Order by must be added

Sako.


Signature IT-Consult Armainak Sarkis
----- Original Message ----- 
From: "John" <mmmmmmmmm53@web.de>
To: <user@cayenne.apache.org>
Sent: Thursday, June 07, 2007 5:38 PM
Subject: Re: Select Query on Table with no Primary Key


> Hello Dave,
>
>
> You said you use a view and you will NOT modify the data, then try this:
>
> Your View name : MyView.
>
> --------------------------------------------------------------------------------------------------------------------------
> create another view call MyViewWithPK
> -------------------------------------------------
>
>
> SELECT e.* , (SELECT COUNT(*) FROM MyView e2 WHERE e2.name <= e.name ) AS 
> rownumber
> FROM MyView e
>
> // remember that name must be included in the view MyView
> // remember that this technique is slow, but i suppose this is your last 
> problem :)
> ---------------------------------------------------------------------------------------------------------------------------
> let me know if this work :)
>
>
> Sako
>
> Signature IT-Consult Armainak Sarkis
> ----- Original Message ----- 
> From: "Dave Merrin" <dmerrin@ipasystems.co.uk>
> To: <user@cayenne.apache.org>
> Sent: Thursday, June 07, 2007 3:36 PM
> Subject: Re: Select Query on Table with no Primary Key
>
>
>> Hi Andrus,
>>
>> I was playing about with putting in my own EntityResolver. For now I'm 
>> going to take your approach and see how I get on.
>>
>> Cheers,
>>
>> Dave
>>
>> Andrus Adamchik wrote:
>>> To give an example - if you have an EMPLOYEE table, you can make an 
>>> assumption that it is highly unlikely that there are two people with the 
>>> same name, born on the same date, and working in the same department. On 
>>> that assumption you can mark these 4 columns as PK in the Modeler: 
>>> FIRST_NAME, LAST_NAME, DATE_OF_BIRTH, DEPARTMENT_ID.
>>>
>>> Works well with views or tables (updateable or read-only) on any DB.
>>>
>>> Andrus
>>>
>>>
>>> On Jun 7, 2007, at 1:29 PM, Andrus Adamchik wrote:
>>>
>>>> Tore is right - for Cayenne to handle an object (whether read-only on 
>>>> read/write), it needs to know which column or columns uniquely identify 
>>>> each row. Now... you can fake a PK in your model, even if there's none 
>>>> in the db - just select a really unique combination of columns, and 
>>>> mark those columns as the PK in the modeler. I've mapped tables with 
>>>> such "imaginary PK" a lot.
>>>>
>>>> If it is not possible (i.e. duplicate rows are expected to be fetched), 
>>>> you will have to use DataRows.
>>>>
>>>> Andrus
>>>>
>>>>
>>>> On Jun 7, 2007, at 1:04 PM, Tore Halset wrote:
>>>>
>>>>> On Jun 6, 2007, at 16:12 , Dave Merrin wrote:
>>>>>
>>>>>> I'm trying to run a SelectQuery on a table with no primary key. 
>>>>>> Unfortunately it's not working. Can anybody help? I have no control

>>>>>> over the database so I can't add in primary keys.
>>>>>
>>>>> As you know the PK are essential not only to update a row, but to make

>>>>> sure a single row maps to a single DataObject in your context.
>>>>>
>>>>> Some database engines do have a unique invisible column. If your 
>>>>> database does this, then perhaps you could map that column as your 
>>>>> primary key? What database engine are you using?
>>>>>
>>>>> Regards,
>>>>>  - Tore.
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
> 


Mime
View raw message