Using views

HI,
I have created a view in my database that collect data from several Joomla tables
When I try to define a database in Joodb using this view I got this message
The table has no primary index. Please define a ID field using PhpmyAdmin.

As far as I know I cannot define a primary index in a view.

In addition to that the first field of this view is the id field of the joe_users table.

In other Joomla installation I'm using Joodb version 1.5 beta that works perfectly with views.

How can I solve this problem?

Bart wrote: The table has no primary index. Please define a ID field using PhpmyAdmin.

Sometimes it is a good Idea to read Error messages carefully and google for result
ID field using PhpmyAdmin

Your table structure is incorrect. Every table should have an AUTO_INCREMENT primary key field. Usually this field is called id pr ID. Compare with the other tables in your database.

BTW: What do you mean with VIEW? A mysql VIEW?

This is the table I'm trying to define in Joodb
As you can see this is not a table but a logical view


CREATE VIEW Fatture AS

SELECT
u.id as id,

(
CASE WHEN u.name LIKE '%\n%' THEN
TRIM(substring_index(u.name,x'0A',-1))
WHEN u.name LIKE '%\\\%' THEN
TRIM(substring_index(u.name,'\\n',-1))
END
) as Cognome,

(
CASE WHEN u.name LIKE '%\n%' THEN
TRIM(substring_index(u.name,x'0A',1))
WHEN u.name LIKE '%\\\%' THEN
TRIM(substring_index(u.name,'\\n',1))
END
) as Nome,

u.username as username,
u.usertype as usertype,
u.email as email,

t23.fvalue as NumFatt,
t17.fvalue as IntFatt,
t18.fvalue as IndFatt,
t20.fvalue as CapFatt,
t19.fvalue as CityFatt,
t21.fvalue as IvaCfFatt


FROM
jos_users as u

LEFT OUTER JOIN jos_juser_extended_data as t23 ON u.id = t23.user_id and t23.field_id = 23
LEFT OUTER JOIN jos_juser_extended_data as t17 ON u.id = t17.user_id and t17.field_id = 17
LEFT OUTER JOIN jos_juser_extended_data as t18 ON u.id = t18.user_id and t18.field_id = 18
LEFT OUTER JOIN jos_juser_extended_data as t20 ON u.id = t20.user_id and t20.field_id = 20
LEFT OUTER JOIN jos_juser_extended_data as t19 ON u.id = t19.user_id and t19.field_id = 19
LEFT OUTER JOIN jos_juser_extended_data as t21 ON u.id = t21.user_id and t21.field_id = 21

WHERE t23.fvalue IS NOT NULL

Order by NumFatt

Joodb needs a primary auto_increment field.
Explain: "You can only add new data automatically if you have such a auto-generated key"

So it was a bug not a feature that you where able to use a view as MYSQL table.

I have seen your VIEW and it makes no sense. Why do you use multiple tables for only one value per table.

With the pro-version you can easily use the jos_user table as your main table and add the view as a 1:1 linked table.

Alternatively you can manipulate the entry in jos_joodb after the first setup and change the name of the table "jos_user" to the name of your view.

Thank you for your answer Dirk

Let me make some comment on your post

dirk wrote: So it was a bug not a feature that you where able to use a view as MYSQL table.

I'm sorry, I was not aware that accessing in read/only an SQL View was not possible. I found that bug really useful to provide valuable service to our customers.

dirk wrote: I have seen your VIEW and it makes no sense. Why do you use multiple tables for only one value per table?

Before saying that my view make no sense you should know the structure of the tables I'm joining with that view. If you are curious I can provide you with all the details of that tables and I'm quite sure you can agree with me that my sql statements does make sense. Or you can suggest me a better way to retrieve that info.

dirk wrote: With the pro-version you can easily use the jos_user table as your main table and add the view as a 1:1 linked table.

Alternatively you can manipulate the entry in jos_joodb after the first setup and change the name of the table "jos_user" to the name of your view.

I will try this one and let you know.

Thanks

I will not remove this inquiry from the code. But you can do.

In JooDBAdminHelper there is a function called selectFieldTypes

Replacewith

Bart wrote: If you are curious I can provide you with all the details of that tables and I'm quite sure you can agree with me that my sql statements does make sense.

Yes please send em to service a feenders.de

But there is strange code in your view. You split the name field at a linebreak position to get prename and surname. The joomla username is a normal varchar field which normally does not contain any linebreaks.