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

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

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

hey Stanton

plz send the right sql query .

It seems every thing is fine!

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.