Is it possible to link CC/CF to a saved database view rather than a table?

I am wondering whether it is possible to link CC/CF to a view that I have saved in my MySQL database, rather than a table.

I'm using the Community Builder add-in which creates a secondary table to store additional user data. I'd like to be able to have one view that pulls e-mail address from the base users table, and my custom fields from the Community Builder table.

I've created a View in the back end, which does have a unique identifier, and is editable in MySQL. When I select it as my CC Table name, the display of the data table is fine. However, when I try to use the Edit link to go to a CF data form, I get a SQL error with an error in the WHERE statement... FROM `ViewName` AS `Model` WHERE `Model`.` IS NULL

Is this anything I can fix? Or is it just not possible to use a View rather than a Table?

Thanks.

Hi farmington,

A year or so ago I did manage to get CF or CC v4 (I forget which) to connect to a view but it required a minor hack in the code to let the View query work. I haven't tried with v5 but I imagine that it could be done.

Bob

Hi,

What's the exact error ? please post a screenshot for the Model config as well!

Regards,
Max

OK, here's a short description of what I'm trying to accomplish. I'm using Community Builder, which has a second table for user details called jos_comprofiler. When I create a CC connection and CF form that connect to that table, it's fine. But I also want to be able to edit email, which is in the jos_users table. I've created a View that joins those two tables, called jos_UserView.

When the CC connection is pointing to the jos_comprofiler table, it works fine, it just doesn't display email (or pass it to the CF).

When the CC connection is pointing to the jos_UserView view, the CC view of the data looks fine. But when I click on the Edit link, I get this:

Unknown column 'Residents. IS NULL' in 'where clause' 
SQL=SELECT `Residents`.`id` AS `Residents.id`, 
`Residents`.`name` AS `Residents.name`,
`Residents`.`username` AS `Residents.username`, 
`Residents`.`email` AS `Residents.email`, 
`Residents`.`firstname` AS `Residents.firstname`, 
`Residents`.`middlename` AS `Residents.middlename`, 
`Residents`.`lastname` AS `Residents.lastname`, 
`Residents`.`cb_housenumber` AS `Residents.cb_housenumber`, 
`Residents`.`cb_dir` AS `Residents.cb_dir`, 
`Residents`.`cb_street` AS `Residents.cb_street`, 
`Residents`.`cb_phone` AS `Residents.cb_phone`, 
`Residents`.`cb_include_in_directory` AS `Residents.cb_include_in_directory`, 
`Residents`.`cb_prefer_paper` AS `Residents.cb_prefer_paper`, 
`Residents`.`cb_prefer_electronic` AS `Residents.cb_prefer_electronic`,
`Residents`.`cb_address` AS `Residents.cb_address`, 
`Residents`.`cb_home_id` AS `Residents.cb_home_id` 
FROM `jos_UserView` AS `Residents` WHERE `Residents`.` IS NULL

Ok, what do you have in the "Conditions" box ? you didn't post the model setup screenshot!

Also, does the users table get updated when you update the view ?

You could also do the Join using CC directly if you like!

Sorry, I couldn't paste a screen cap, and there really isn't much to the model. Here are the inputs:
Model Title: Residents
Table Name: jos_UserView
Conditions:
Fields:
Order: lastname, firstname
Group:

Yes, if you update the data through the view in phpMyAdmin, the change does save to the jos_users table.

I had not found a method to do the join using CC. I will research that.

Hi farmington,

Please remove the order fields, save then click the "delete cache" button.

I have just tested a very basic connection to a single table view and it works fine.

Regards,
Max

I did have order fields in my CC5 model, so I removed those and added them to the view in the back end. I deleted the cache for both CC5 and CF5, but I still get the same area.

I'll send you my credentials via PM and let you take a look at it, if you want.

Hi farmington,

I have just tested this, the problem happens only when you try to edit, because the view does not have a primary key, a CC requires a primary key value passed for the record to be edited.

So a new feature may be needed to solve this, but it should not be complicated to just build the JOIN using CC itself, please give it a try, its explained on the FAQs section.

Regards,
Max

Hi

I just discovered the same hitch with using views http://www.chronoengine.com/forums/posts/f12/t98614/how-to-build-sort-column-in-custom-rather-than-table-view.html. My problem is that I need to derive local dates to select records and hence needed that view. I was going to try to derive fields using the PHP function. But to which FAQ are you referring Max?

Nick

Hi Nick,

I was talking about joining another model to the main model in CC instead of using a view to do this, this is explained on the FAQs here:
http://www.chronoengine.com/faqs/72-ccv5/5218-adding-1-more-belongsto-model-to-our-connection.html

Regards,
Max

Hi Max

Thanks - got it. But in my case of needing derived fields (local date) from a table field that would not suit I suspect. I wondered about setting up a derived filed with a trigger in mySQL but that looks a bit suspect.

See how I go.

Thanks

Nick

Hi Nick,

I couldn't understand this, what are you trying to do with that field exactly ?

Regards,
Max

Hi Max
I have a table with a field being the UTC date that an invoice was paid. I want to set up a selection being the local date that the invoice was paid. So I set up a view that had two fields - the original UTC date and a derived date being a conversion of that UTC to a local date. I use the latter in a drop down to pick a date and the then the related UTC to pick records.

Does that make more sense?

Ok, why not store the local dates into another table with a foreign key and do a "hasOne" join ?

And if the conversion can be done using PHP then you can do it when the record is displayed, it doesn't have to be stored anywhere!

Hi Max

Ok, why not store the local dates into another table with a foreign key and do a "hasOne" join ?

. Apart from matters of redundant data 🧐 I don't think that would work as some of the fields I want to use in other connections involves some internal Joomla tables (#__users for example) and I wouldn't want to alter the code to write to that second table.

And if the conversion can be done using PHP then you can do it when the record is displayed,

Indeed but since I want to use the local dates as a search option (via a drop down list of all dates used) I need that information before I show the data.

I still see a few options
[list]Use a PHP field to return the local dates[/list]
[list]Use a date control but apart from not yet got that to work properly there might be some awkward code to convert that date from local to a pair of UTC dates. [/list]
[list]build a view with the key field and local dates and link that by adding the model[/list]

I think i will try the view path first.

Thanks for the help.

Nick

It looks like this will work.

build a view with the key field and local dates and link that by adding the model

I have the local dates in a related model

-- CREATE OR REPLACE VIEW hvu3a_u3a_vu_CC_courseReceiptsLocalDates AS
SELECT
`idReceipt`,
DATE_FORMAT(CONVERT_TZ(`datePaid`,'GMT','Australia/Sydney'),'%d %b %y  %h:%i %p') AS `localDatePaid`,
DATE_FORMAT(CONVERT_TZ(`dateReceived`,'GMT','Australia/Sydney'),'%d %b %y %h:%i %p') AS `localDateReceived`,
DATE_FORMAT(CONVERT_TZ(`datePaid`,'GMT','Australia/Sydney'),'%Y-%m-%d') AS `pickDatePaid`,
DATE_FORMAT(CONVERT_TZ(`dateReceived`,'GMT','Australia/Sydney'),'%Y-%m-%d') AS `pickDateReceived`
FROM `hvu3a_u3a_receiptsCourses` 

and I reckon with a few tweaks i am out of the woods. at least for the moment.



:ugeek:

Nick

I've been able to do OK using multiple Models in my CC. However, I'm still finding it useful to use views to do things like formatting in the back end that I can't make work in the CC front end. For example, I'm using CONCAT() to prepend a "$" to fields that contain dollar amounts, or doing a DATE_FORMAT on a date. I've tried doing that in the Field list of my model or the Columns list of my Front List, and it doesn't seem to work. So, I'm doing it in a view, and then displaying it in CC. Being able to do the usual "Edit" function from there would still be useful. If there's a way to do the formatting on the front end, that could presumably solve the problem also. Thanks.

Hi

I have only just used multiple modelds - I found that I needed to do that in order to make things like Binary work. Which it does. I am struggling now to make the filter work for the second model.

As to formatting - I have been using custom connections and PHP so.

    <tr>
      <td class='njjoCenter'>{mdlListCourseReceipts.accountPaid}</td>
      <td class='njjoCenter njjoborderrightThick'><img src='components/com_u3a/common/img/print.png' /></td>
      <td class='tdidReceipt njjoCenter pad2 njjoborderright' >{mdlListCourseReceipts.idReceipt}</td>
      <td class='tdreceipttype njjoCenter pad2 njjoborderrightThick'>{mdlListCourseReceipts.enmReceiptType}</td>
      <td class='tddatePaid pad2 njjoCenter njjoborderright'>{mdlCourseLocalDates.localDateReceived}</td>
      <td class='tdname pad2 njjoborderrightThick'><?php echo $bookedBy; ?></td>
      <td class='tdfeePaid njjoRight pad2 njjoborderright'>${mdlListCourseReceipts.feePaid}</td>
      <td class='tdpaidBy njjoCenter pad2 njjoborderright'><?php echo $paidBy; ?></td>
      <td class='tddatePaid pad2 njjoCenter njjoborderright'>{mdlCourseLocalDates.localDatePaid}</td>
      <td class='tdpayReceivedBy njjoCenter pad2 njjoborderrightThick'>{mdlListCourseReceipts.payReceivedBy}</td>
      <td class='njjoCenter'><img src='components/com_u3a/common/img/edit.png' /></td>
      <td class='njjoCenter'><img src='components/com_u3a/common/img/cancel.jpg' /></td>
    </tr>

via a 'require_once file.

As you can see you can reference the second model OK and use CSS to format fields.

Nick