Early Bird Registration for DrupalCon Portland 2024 is open! Register by 23:59 PST on 31 March 2024, to get $100 off your ticket.
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
Comment #1
mikeryanIt 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.
Comment #2
ggalan1173 CreditAttribution: ggalan1173 commentedthanks, that's what I did :)
Comment #3
TylerE CreditAttribution: TylerE commentedI'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.
Comment #4
drupal-fan-4u CreditAttribution: drupal-fan-4u commentedI 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
Comment #5
mikeryanYou 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.
Comment #6
drupal-fan-4u CreditAttribution: drupal-fan-4u commentedWorks like a charm. I cant imagine how many people have wanted to expose a mysql view to drupal views . Thanks so much!
Comment #7
drupal-fan-4u CreditAttribution: drupal-fan-4u commentedAlso,
If you need a unique value for a mysql view, try using 'UUID() as id...'
Comment #8
mikeryanWell, that was easier that I expected...