I want to find out how many products are underneath that category and How do I use defaultifempty ()

Unal UN asked on January 2, 2018 12:15

I am trying to combine two tables. I want to find out how many products are underneath that category. Can you be a helper?

How do I use defaultifempty () as an attachment ?

Code Samples var result = CustomTableItemProvider.GetItems("customtable.MyJoinCategory").Source(s => s.LeftJoin("customtable_MyJoinProduct", "ItemID", "CategoyID")).Column("Name,ProductName").Result;

Samples Result

CategoryName Product Count()

Category 1=>3

Category 2=>0

Recent Answers

David te Kloese answered on January 2, 2018 14:37

Guess this is the same question as in your other ticket?

https://devnet.kentico.com/questions/i-am-trying-to-combine-two-tables-i-want-to-find-out-how-many-products-are-underneath-that-category

0 votesVote for this answer Mark as a Correct answer

Mariia Hrytsai answered on January 2, 2018 14:37 (last edited on January 2, 2018 14:37)

Try this:

CustomTableItemProvider.GetItems("customtable.MyJoinCategory").Source(s => s.LeftJoin("customtable_MyJoinProduct", "ItemID", "CategoyID")).Column("Name,ProductName").AddColumn( new CountColumn("ItemID").As("ProductCount") ).GroupBy("CategoyID").Result;

Please see this article for details: https://devnet.kentico.com/articles/kentico-8-technology-dataquery-api

1 votesVote for this answer Mark as a Correct answer

Unal UN answered on January 3, 2018 08:58

Hi MARiia,

I am Sorry,it's not working :(

0 votesVote for this answer Mark as a Correct answer

Unal UN answered on January 8, 2018 08:41

Mariia Hrytsai

Can you help please ?

Not working ! CustomTableItemProvider.GetItems("customtable.MyJoinCategory").Source(s => s.LeftJoin("customtable_MyJoinProduct", "ItemID", "CategoyID")).Column("Name,ProductName").AddColumn( new CountColumn("ItemID").As("ProductCount") ).GroupBy("CategoyID").Result;

0 votesVote for this answer Mark as a Correct answer

Mariia Hrytsai answered on January 9, 2018 10:07

You can write your custom query in sql that will return desired result, and than use

var query = new DataQuery("customqueryname");
var result = query.Result;

0 votesVote for this answer Mark as a Correct answer

Unal UN answered on January 16, 2018 15:20

Thank you

I Solved

Code Samples

var CategoryAndProductCustomColumuns2 = CustomTableItemProvider.GetItems("customtable.MyJoinCategory").Source(s => s.LeftJoin("customtable_MyJoinProduct", "ItemID", "CategoryID")).Column("Name").AddColumn(new CountColumn("CategoryID").As("ProductCount")).GroupBy("CategoryID,Name").Result;

0 votesVote for this answer Mark as a Correct answer