Query .Join joining on wrong Table

Topics: Customizing Orchard, Writing modules
Jan 10, 2013 at 12:37 PM

Hi There

I am trying to create a query on a ContentType and Join to another contentType but the query is returning the incorrect results and joining on the wrong table.

- Model

 

 public class FeaturedItemPartRecord : ContentPartRecord {

        public FeaturedItemPartRecord()
        {
            VehicleRangePartRecord = new VehicleRangePartRecord();
        }

        public virtual string Headline { get; set; }
        public virtual string SubHeadline { get; set; }
        public virtual string LinkUrl { get; set; }
        public virtual bool SeparateLink { get; set; }
        public virtual string LinkText { get; set; }
        public virtual string GroupName { get; set; }
        public virtual int SlideOrder { get; set; }
        public virtual int RangeId { get; set; }

        public virtual VehicleRangePartRecord VehicleRangePartRecord { get; set; }
    }

--Migration

 public int UpdateFrom103()
        {
            SchemaBuilder.AlterTable("FeaturedItemPartRecord", builder => builder
                    .AddColumn<int>("VehicleRangePartRecord_Id"));

            return 104;
        }

--Implementation

 

 protected override DriverResult Display(FeaturedItemSliderWidgetPart part, string displayType, dynamic shapeHelper) {
            int slideNumber = 0;

            var featuredItems = _contentManager.Query<FeaturedItemPart, FeaturedItemPartRecord>("FeaturedItem")
                .Where(fip => fip.GroupName == part.GroupName)
                .OrderBy(fi => fi.SlideOrder)
               // .Join<VehicleRangePartRecord>()
                .List()
                .Select(fi => new FeaturedItemViewModel {
                    Headline = fi.Headline,
                    SubHeadline = fi.SubHeadline,
                    LinkUrl = fi.LinkUrl,
                    SeparateLink = fi.SeparateLink,
                    LinkText = fi.LinkText,
                    ImagePath = fi.Fields.Single(f => f.Name == "Picture").Storage.Get<string>(""),
                    SlideNumber = ++slideNumber,
                    RangeName = fi.VehicleRange.Name
                }).ToList();

            var group = _contentManager.Query<FeaturedItemGroupPart, FeaturedItemGroupPartRecord>("FeaturedItemGroup")
                .Where(fig => fig.Name == part.GroupName)
                .List()
                .SingleOrDefault();

            return ContentShape("Parts_FeaturedItems",
                () => shapeHelper.Parts_FeaturedItems(FeaturedItems: featuredItems, ContentPart: part, Group: group));
        }

 

--Results

 

SELECT        //Records
FROM        Orchard_Framework_ContentItemVersionRecord this_
inner        join Orchard_Framework_ContentItemRecord contentite1_
                on this_.ContentItemRecord_id=contentite1_.Id
inner join    FeaturedItemSlider_FeaturedItemPartRecord featuredit3_   
                on contentite1_.Id=featuredit3_.Id
inner join    Hellocomputer_Toyota_Vehicles_VehicleRangePartRecord vehicleran4_
                on contentite1_.Id=vehicleran4_.Id
inner join    Orchard_Framework_ContentTypeRecord contenttyp2_
                on contentite1_.ContentType_id=contenttyp2_.Id
WHERE        contenttyp2_.Name in ('FeaturedItem') and featuredit3_.GroupName = 'Home Page Slider' and this_.Published = 1

 

This join is should be joining FeatureItemPartRecord to VehicleRangePartRecord.

inner join    Hellocomputer_Toyota_Vehicles_VehicleRangePartRecord vehicleran4_
                on contentite1_.Id=vehicleran4_.Id

 

Even though the ID would be the same its not returning any results.

 

Any help would be appreciated

 

Thanks

Stanton

 

 

Coordinator
Jan 10, 2013 at 4:39 PM

Seems like you have a repro, so please file a bug.

Jan 11, 2013 at 5:21 AM

I think I found the Issue It looks like nhibernate is not creating the mapping as I tried to access the Vehicle Object through the Navigation property and its returning null.

Is there a way to view the mapping or recreate them.

 

 public class FeaturedItemPartRecord : ContentPartRecord {

        public FeaturedItemPartRecord()
        {
            VehicleRangePartRecord = new VehicleRangePartRecord();
        }

        public virtual string Headline { get; set; }
        public virtual string SubHeadline { get; set; }
        public virtual string LinkUrl { get; set; }
        public virtual bool SeparateLink { get; set; }
        public virtual string LinkText { get; set; }
        public virtual string GroupName { get; set; }
        public virtual int SlideOrder { get; set; }
        public virtual int RangeId { get; set; }

        public virtual VehicleRangePartRecord VehicleRangePartRecord { get; set; }
    }

 public class FeaturedItemPart : ContentPart<FeaturedItemPartRecord> {
        
        public string Headline {
            get { return Record.Headline; }
            set { Record.Headline = value; }
        }

        public string SubHeadline {
            get { return Record.SubHeadline; }
            set { Record.SubHeadline = value; }
        }

        public string LinkUrl {
            get { return Record.LinkUrl; }
            set { Record.LinkUrl = value; }
        }

        public bool SeparateLink {
            get { return Record.SeparateLink; }
            set { Record.SeparateLink = value; }
        }

        public string LinkText {
            get { return Record.LinkText; }
            set { Record.LinkText = value; }
        }

        public string GroupName {
            get { return Record.GroupName; }
            set { Record.GroupName = value; }
        }

        public int SlideOrder
        {
            get { return Record.SlideOrder; }
            set { Record.SlideOrder = value; }
        }

        public int RangeId
        {
            get { return Record.RangeId; }
            set { Record.RangeId = value; }
        }

        public VehicleRangePartRecord VehicleRangePartRecord
        {
            get { return Record.VehicleRangePartRecord; }
            set { Record.VehicleRangePartRecord = value; }
        }
        
    }

    protected override DriverResult Editor(FeaturedItemPart part, dynamic shapeHelper) {
            var groups = _contentManager.Query<FeaturedItemGroupPart, FeaturedItemGroupPartRecord>("FeaturedItemGroup").List();
            var ranges = new SelectList(_contentManager.Query<VehicleRangePart, VehicleRangePartRecord>("VehicleRange").List(),"Id","Name",part.VehicleRangePartRecord.Id);
            var viewModel = new FeaturedItemEditViewModel {FeaturedItem = part, Groups = groups.ToList() , Ranges = ranges};
            return ContentShape("Parts_FeaturedItem_Edit",
                () => shapeHelper.EditorTemplate(TemplateName: "Parts.FeaturedItem.Edit", Model: viewModel));
        }
 

part.VehicleRangePartRecord.Id returns null
Jan 11, 2013 at 12:04 PM

hey Stanton

plz send the right sql query .

It seems every thing is fine!

Jan 11, 2013 at 10:10 PM

Orchard will recreate the mappings,bin file whenever the DB schema has changed or simply when the file does not exist, so you can delete it to have it recreated.