I have a problem that I can't solve in a good way.
Here is what I got so far.
An Item can have many SubItems
Code:
[Class( Table = "SubItems", Lazy=true )]
public class SubItem
{
private int id;
private int sortOrder;
private int balance;
private Item item;
ISet<Hairless.Entities.Attribute.Attribute> attributes = new SortedSet<Hairless.Entities.Attribute.Attribute>();
[Set(Lazy = true, Table = "Attributes",Cascade=CascadeStyle.SaveUpdate)]
[Key(1, Column = "SubItemId")]
[OneToMany(2, ClassType = typeof(Hairless.Entities.Attribute.Attribute))]
public virtual ISet<Hairless.Entities.Attribute.Attribute> Attributes
{
get { return attributes; }
set { attributes = value; }
}
[Id(Name = "Id", Column = "SubItemId")]
[Generator(1, Class = "identity")]
public virtual int Id
{
get { return id; }
set { id = value; }
}
[Property]
public virtual int SortOrder
{
get { return sortOrder; }
set { sortOrder = value; }
}
[Property]
public virtual int Balance
{
get { return balance; }
set { balance = value; }
}
[ManyToOne(Column = "ItemId", NotNull = true)]
public virtual Item Item
{
get { return item; }
set { item = value; }
}
public virtual void addAttribute(Hairless.Entities.Attribute.Attribute attribute)
{
attributes.Add(attribute);
attribute.SubItem = this;
}
}
An element class is connected to a Subcategory. An Element class is like "Size" , "Color". Every Item also has one SubCategory.
Code:
[Class(Table = "Elements")]
public class Element
{
private int id;
private String name;
private int sortOrder;
private Hairless.Entities.Category.SubCategory subCategory;
[Id(Name = "Id", Column = "ElementId")]
[Generator(1, Class = "identity")]
public virtual int Id
{
get { return id; }
set { id = value; }
}
[ManyToOne(Column = "CategoryId", NotNull = true)]
public virtual Hairless.Entities.Category.SubCategory SubCategory
{
get { return subCategory; }
set { subCategory = value; }
}
[Property(Length=255,NotNull=false)]
public virtual String Name
{
get { return name; }
set { name = value; }
}
[Property(NotNull=false)]
public virtual int SortOrder
{
get { return sortOrder; }
set { sortOrder = value; }
}
}
An Attribute has one Element and is Connected to a SubItem
Code:
[Class(Table = "Attributes", Lazy = false)]
public class Attribute
{
private int id;
private Element element;
private Hairless.Entities.Item.SubItem subItem;
private String data;
[Id(Name = "Id", Column = "AttributeId")]
[Generator(1, Class = "identity")]
public virtual int Id
{
get { return id; }
set { id = value; }
}
[ManyToOne(Column = "SubItemId", NotNull = true)]
public virtual Hairless.Entities.Item.SubItem SubItem
{
get { return subItem; }
set { subItem = value; }
}
[ManyToOne(Column = "ElementId", NotNull = true)]
public virtual Hairless.Entities.Attribute.Element Element
{
get { return element; }
set { element = value; }
}
[Property]
public virtual String Data
{
get { return data; }
set { data = value; }
}
}
I would like to create a Criteria query to get A ResultTransformer for an item.
An Element could look like this.
Id = 1
SubCategory = 1
SortOrder = 1
Name = "Size"
Id = 2
SubCategory = 1
SortOrder = 2
Name = "Color"
Sub item
Id = 1
ItemId=1
SortOrder = 1
Balance = 2
Id = 2
ItemId=1
SortOrder = 2
Balance = 3
An attribute
Id = 1
ElementId = 1
Data = "Medium"
SubItem = 1
Id = 2
ElementId = 2
Data = "Red"
SubItem = 1
Id = 3
ElementId = 1
Data = "Small"
SubItem = 2
Id = 4
ElementId = 2
Data = "Orange"
SubItem = 2
What I would like is to get a result like this
Transformed subitem
id = 1
balance = 2
attribute = "Medium / red";
id =2
balance = 3
attribute = "Small/ Orange";
The sort must be done with SubItem.SortOrder, Element.SortOrder.
I can't get this query to work. Is it possible to just get Columns and Iterate and build the special subitem afterwards or can this be done in one query?
This is what I have so far in pure SQL. I would like to have this is Criteria. Problem is that it returns multiple subitems but I guess I could iterate afterwards. Can I just get these 3 coumns like column[ 0 ], [ 1 ] and so on?
Code:
SELECT s.SubItemId, s.Balance , attr.Data FROM Items i, SubItems s, ( SELECT a.* FROM Attributes a, Elements e WHERE e.ElementId = a.ElementId ORDER BY e.SortOrder ) as attr WHERE i.ItemId = 80
AND i.ItemId = s.ItemId AND s.SubItemId = attr.SubItemId ORDER BY s.SortOrder
Login
FAQ
Search
