Hi. To simplify my data model I create a view (a database view...) joining node, user, and other tables and then I added in Table Wizard.
The problem is that I get the following error:

full_lead has no primary key defined. A single-field primary key is necessary to use this table as the base table for a view.

I can not define a primary key as long as MySql does not support pk on views. The view is a select with an Union, and one of the column IS (and can work as) the PK of the result set.
Is there something I can do?

thanks

Comments

mikeryan’s picture

It is a Views module restriction that for a table to serve as a base table, it must have a single primary key field. Thus, Table Wizard cannot create a default view for a table which does not have such a PK. However, if you can create a table with a PK corresponding to the logical key of your view, you could create a view using that as the base table and joining to the MySQL view.

ggalan1173’s picture

Status: Active » Closed (fixed)

thanks, that's what I did :)

TylerE’s picture

Status: Closed (fixed) » Active

I'd like to reopen this.

This functionality would be useful to me.

Would it be possible to allow us to *specify* a column as a PK, so that views is happy? That pushes the responsibility on to the user to make sure that the that column is stable and unique, but I'm willing to shoulder that burden if it means we can do views on views.

drupal-fan-4u’s picture

I also would like to specify a primary key for Drupal Views. I have some mysql views that show up as not having a primary key... I have made sure the field is unique by using UUID() in my sql view.

Has anyone found a way around this?

Thanks

mikeryan’s picture

You could try manually telling TW what your PK is - look up your "table" (MySQL view) twtid in tw_tables, then in tw_columns find the PK for that twtid and set primarykey=1. You'll probably have to clear your cache for the Views hooks to fire... If that does work, let me know - we can add the support to the UI to specify the PK.

drupal-fan-4u’s picture

Works like a charm. I cant imagine how many people have wanted to expose a mysql view to drupal views . Thanks so much!

drupal-fan-4u’s picture

Also,
If you need a unique value for a mysql view, try using 'UUID() as id...'

mikeryan’s picture

Version: 6.x-1.1-rc2 » 6.x-1.x-dev
Component: Miscellaneous » Code
Assigned: Unassigned » mikeryan
Category: support » feature
Status: Active » Fixed

Well, that was easier that I expected...

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.