Hey Zac,

You could easily create a custom view for that, check the sourcecode (https://github.com/TimGeyssens/UIOMatic/tree/master/src/UIOMatic/App_Plugins/UIOMatic/backoffice/views) for the default views will try to give you some pointers but limited time now so will be in the new year, happy holidays!

Thanks Tim. I'm an angular novice so wasn't quite sure what to do at first, but I've found a solution. I modified the existing Label view - creating a new one just seemed a bit redundant. I added a controller that checks if a custom Config and typeName have been passed through, and if so it uses the getById method to pull back the data, and the applies the textTemplate.

I added a new controller in the views folder called label.controller.js (this needs including in the manifest of course):

angular.module("umbraco").controller("UIOMatic.Views.Label",
function ($scope, uioMaticObjectResource, $parse) {
    //example config
    //{'typeName': 'Example.Models.Person, Example', 'textTemplate' : 'FirstName + \" \"+ LastName '}

    function init() {
        if ($scope.property.Config && $scope.property.Config.typeName)
            uioMaticObjectResource.getById($scope.property.Config.typeName, $scope.property.Value).then(function (response) {
                var template = $parse($scope.property.Config.textTemplate);
                $scope.property.Value = template(response.data);
            });
    }

    $scope.$on('ValuesLoaded', function (event, data) {
        init();
    });

});

The label.html view needs updating:

<div ng-controller="UIOMatic.Views.Label">{{property.Value}}</div>

and it works, with the property decorated thusly:

[UIOMaticField("Owner", "", View = "Label", Config = "{'typeName': 'Example.Models.Person, Example', 'textTemplate' : 'FirstName' }")]
public int OwnerId { get; set; }

Of course this plays nice with normal labels since the $scope.propery.Value isn't touched if there is no typeName passed through in the config.

Edit: Updated the controller to wait on the ValuesLoaded event as per the other existing controllers.

Nice, thanks for sharing!

Hi Tim,

So, I'm able now to see the owner of a dog in the dog edit screen. However, I also want to show the owner of a dog in the dogs table listview. In fact this is fairly important as I want to be able to search and filter my dogs by their owner's names.

So what I tried to do then is create a property in my equivalent of the dog UIOMatic class, where the ColumnAttribute name is Owner.FirstName:

    [Column("Owner.FirstName")]
    [UIOMaticIgnoreField] //Not needed in edit view due to label for the OwnerId property
    public string OwnerFirstName { get; set; }

Obviously this won't get populated by the SQL that would be automatically generated:

SELECT * FROM DOG

So I hooked into the BuildingQuery event to try to join onto the Owner table:

    private void PetaPocoObjectController_BuildingQuery(object sender, QueryEventArgs e)
    {
        if (e.TableName == "Dog")
        {
            e.Query.InnerJoin("Person").On("Dog.OwnerId = Person.Id");
        }
    }

However the problem is that my Owner.FirstName won't match up with FirstName in the query in PetaPocoObjectController so the property isn't bound. Why don't I use FirstName instead for the ColumnAttribute name? Because in reality I need the prefix to stop conflicts. As a contrived example, I may also want the FirstName of some other relation to a Dog, e.g. DogWalker.FirstName.

So I figured I need to modify the sql query so that I can select Owner.FirstName with a prefix, however if I try to do that in in the event handler, I can only instantiate a new instance of Umbraco.Core.Persistence.Sql since there doesn't appear to be a way to modify the existing SELECT * part and this doesn't seem to make it back to PetaPocoObjectController, which retains the original query.

Am I being stupid? How can I show my bound OwnerFirstName property in the listview?

Hmm must say that I haven't tried an inner join, will give it a try and report back on how to proceed

From what it looks like we'll need to use the ResultColumn attribute from petapoco http://www.toptensoftware.com/blog/posts/101-PetaPoco-Working-with-Joins

And then use the PetaPoco.Sql.Builder to recreate and assign the sql statement

So something like this (but ain't working yet)

Add this property to the dog class

    [ResultColumn]
    [UIOMaticIgnoreField]
    public string OwnerName { get; set; }

Then in the event handler

        if (e.TableName == "Dogs")
        {
            e.Query = Umbraco.Core.Persistence.Sql.Builder
                .Append("SELECT Dogs.Id, Dogs.Name, Dogs.IsCastrated, Dogs.OwnerId, People.Firstname as OwnerName")
                .Append("FROM Dogs")
                .Append("INNER JOIN People ON Dogs.OwnerId = People.Id");

        }

But it seems the query isn't being changed so need to figure why that is

Hmm, interesting about the ResultColumn attribute. I thought as long as I could perform the join, we had everything we needed with the ColumnAttribute decoration, because the value for FirstName was present when debugging the result in PetaPocoObjectController line 159: var p = db.Page<dynamic>(pageNumber, itemsPerPage, query);. So I thought it would be set by the subsequent reflection.

So, although the join appeared to work when modifying the existing query, I couldn't modify the select statement - I was having the same problem as you. I figured it was something to do with only being able to update the reference to point to a new query object rather than being able to modify the existing query object. But stepping through, I couldn't see why the query wasn't updated after returning from the event.

yeah think it's being passed by value rather then reference, so will make a small change to update that...

So I have it working with the following code

         if (e.TableName == "Dogs")
        {

            e.Query = Umbraco.Core.Persistence.Sql.Builder
                .Append("SELECT Dogs.Id, Dogs.Name, Dogs.IsCastrated, Dogs.OwnerId, People.Firstname + ' ' + People.Lastname as OwnerName")
                .Append("FROM Dogs")
                .Append("INNER JOIN People ON Dogs.OwnerId = People.Id")
                .Append("ORDER BY Dogs.Id desc");


        }

THat will now output the following list

But of course since now the order by is hard coded, the UI for ordering doesn't do anything...

Great. So I suppose as a rule, any custom SQL added in the BuildingQuery event should never contain an ORDER BY since, as you say, it would break the sorting, but also presumably it would break the filtering since a WHERE clause would be appended on afterwards.

BuildedQuery on the other hand could contain whatever anyone wants, if they fancy doing some dirty SQL string modifications.

I'm having the exact same issue, except with dropdown view.

[Column("TemplateFormatId")]
    [UIOMaticField("Template Format", "template Format", View = "dropdown",
         Config = "{'typeName': 'Portal.Models.TemplateFormat, Portal', 'valueColumn': 'Id', 'sortColumn': 'Name', 'textTemplate' : 'Name'}")]
    public int TemplateFormatId { get; set; }

[ResultColumn]
    [UIOMaticIgnoreField]
    public string TemplateFormat { get; set; }

I have followed the solution as above, but I'm still getting empty columns in the listview

e.Query = Umbraco.Core.Persistence.Sql.Builder
                .Append("SELECT CoopCalendar.Id, CoopCalendar.SubmissionDate, CoopCalendar.LiveDate, CoopCalendar.CarModelId, CoopCalendar.TemplateFormatId, CoopCalendar.XMLSent, CoopCalendar.DueMailSent, CoopCalendar.PreDueMailSent, TemplateFormat.Name as TemplateFormat")
                .Append("FROM CoopCalendar")
                .Append("INNER JOIN TemplateFormat ON CoopCalendar.TemplateFormatId = TemplateFormat.Id");

is working on a reply...