Feature Team: Projector

Topics: Announcements
Coordinator
Oct 5, 2011 at 10:34 PM

This thread is for the Projector feature team. If you want to help with the design, implementation, testing and documentation of this feature, please chime in here.

The Projector feature's goal is to enable the construction of complex content queries from the admin UI and without having to write code. For example, you could build a query to get all blog posts by author "Foo" that have the tag "Orchard", ordered by descending creation date.

Initial members:

  • Sébastien Ros
  • Bertrand Le Roy
Oct 15, 2011 at 2:24 PM

Hi Bertrand,

What is the scope of this component?. Will it have the ability to generate query for any content type in the database?

For our project, we have to do many different lists for a single content type and we expect to setup a team in Nov. 

If you think scope/timelines are aligned, we could align our efforts.

 

I do think for a CMS, such a component would be a major killer feature. Ofcourse, this combined with NHibernate's 2nd tier cache.

-venkat.

Coordinator
Oct 15, 2011 at 4:40 PM

Hi,

I am developing this module right now. It will be able to query anything that is a content item. It means all dynamic type, or Page, Blog Post, ... It won't be able though to query simple records like Tags for instance, though it will be able to use them as filters, like getting all content items with some specific tag.

This module will the star of Orchard 1.4, as it requires some changes it the core, and should be available in November. This is the goal and right now it's on time. You can even follow the development on the 1.x branch. It doesn't work yet, but here is what is done:
- automatic fields indexing into database
- specific FieldsPart automatically attached to content items having fields, so we can query over thir values
- new Content Manager methods for dynamic queries (e.g., query with a string instead of a LINQ expression)
- some dynamic settings management to create queries and derived queries

Next step is to bind a simple UI with filter providers, a filter being a component capable of adding some filtering to a query. There will also be Sorting, Paging, and Projection component.

The 2nd tier cache is part of the Performance feature team effort.

And YES, this will be a killer feature.

Oct 15, 2011 at 6:12 PM

Hi Sebastien,

 

This sounds exciting. This feature will be majorly used by us for a enterprise project. We will be starting development of this in early November, it looks like your dev is already under way.I will contact you in early november on where you are with this project and what help you need?. In the meanwhile i will have someone take a look at the 1.4 code.

 

Thanks,

-venkat

Developer
Oct 24, 2011 at 4:27 PM

Will it be possible to make a Filter that retrieves parameters from querystring (specifying the key to use) ?

e.g : I'd create a query that is used in a projection page or widget and if I pass in the url "?q={search}", it would apply this filter and display corresponding items.

Idem with a form control Id parameter : It could allow to easily put a search filter in the page.

Coordinator
Oct 24, 2011 at 5:02 PM

This will be possible with Projector, yes.

Developer
Oct 31, 2011 at 11:09 AM

I tested what the Views Module in Drupal allowed me to build and it's great to see what you have already implemented in Projector.

Some questions on other cool features that could be added :

- Could the properties have a position to be able to choose their placement ?

- Do you plan to make a Table Layout ?

Properties would be rendered in columns.

The header name would be a parameter.

Iif a sort parameter is activated on the property, the column would be sortable.

- Will you allow to add the filters in the display view with their editors and operators ?

A property would allow to add the filter with a label

An Operator could be selected (equal to, greater than ...) with a default value or exposed to the user that would choose himself the filter. 

Coordinator
Oct 31, 2011 at 4:47 PM

Yes on all of it. A lot of work but I think it's necessary. I have already done some changes to enable the Table layout, which is somehow different than the others. And I am also working on a Calendar layout too, by updating the DateTime field to store using a valid DateTime type instead of a string.

Nov 4, 2011 at 5:38 PM

Hi Sebastien, 

I am curious about the new projector feature. I have come to a stand still on my Orchard website. I have tables in my Orchard database that are already populated with product id's and other items. . But these items are not content created by Orchard. They are tables pulled in from another website/database. Will this Projector feature be able to query these tables and pull the content to display on a page. Again these are not content items created by my Orchard install. They are just tables filled with data.

 

Thanks

 

E

Coordinator
Nov 4, 2011 at 5:41 PM

No, that is not what Projector is designed for.

Nov 4, 2011 at 6:57 PM

@Oceantrain: If you are migrating your data permanently to Orchard, you could write an import tool to transform it into content items. Then, Projectors (and Orchard in general) would work on it.

If not, maybe you could keep the two databases in sync... But that's going to be a bit more complicated.

Nov 4, 2011 at 8:02 PM

Thanks phkuate. My current situation is this: I have a SQl server database where all the tables from my Orchard install are stored. I also have custom tables in the same database that Orchard is not aware of.

What I'm trying to accomplish is:

I have an Geo Zipcode locator using some small javascript and HTML 5. That part works fine. You land on my website and your zip code is displayed to you. What I need is to pull product information from these custom tables in the Orchard database based on your zip code.

I looked at the Search Module code, but again that only queries Orchard tables. Not my custom tables. I'm trying to write an MVC project that will do this. I'm just not sure if it will work in Orchard. I would love to migrate all the data from my custom tables into Orchard tables, but I don't know how. I am an entry level programmer and not too familiar with MVC. Aslo my custom tables will be updated on a regular basis by the client, not me. So, unless there is a way to auto migrate data into Orchard tables on a regular basis, I'm kind of lost. I'm down to 1 week until this project is due.

Coordinator
Nov 4, 2011 at 8:05 PM

You can find examples of queries against data that is in the same database as Orchard in this module: https://bitbucket.org/bleroy/orchardpo

Nov 4, 2011 at 8:21 PM

Thanks Bertrand. I was looking through this module before. Trying to make heads or tails of it. What kind of SQL is this bit of code? Is it Linq or Lambda? or Some kind of NHibernate SQL? I want to make sure I am using the write syntax.

 

   using (var session = _sessionLocator.For(typeof(LocalizableStringRecord))) {
                var query = session.CreateQuery(@"
                    select s
                    from OrchardPo.Models.LocalizableStringRecord as s fetch all properties
                    order by s.Path");

Coordinator
Nov 4, 2011 at 8:37 PM

Yes, that one is HQL. There is another example that uses plain SQL, and another that uses Linq.

Nov 4, 2011 at 8:40 PM

This is the regular SQL syntax? I'll be needing to make some joins too.

 

         var groupTranslations = session.CreateSQLQuery(@"
                    SELECT Localizable.Id, Localizable.Path, Localizable.StringKey,
                           Localizable.Context, Localizable.OriginalLanguageString,
                           Localized.Value as Localized, English.Value as English
                    FROM OrchardPo_LocalizableStringRecord AS Localizable
                    LEFT OUTER JOIN OrchardPo_TranslationRecord AS Localized
                        ON Localizable.Id = Localized.LocalizableStringRecord_id
                        AND Localized.Culture = :culture
                    LEFT OUTER JOIN OrchardPo_TranslationRecord AS English
                        ON Localizable.Id = English.LocalizableStringRecord_id
                        AND English.Culture = N'en-US'
                    WHERE Localizable.Path = :group
                    ORDER BY Localizable.Context + Localizable.StringKey")
                    .AddScalar("Id", NHibernateUtil.Int32)
                    .AddScalar("Path", NHibernateUtil.String)
                    .AddScalar("StringKey", NHibernateUtil.String)
                    .AddScalar("Context", NHibernateUtil.String)
                    .AddScalar("OriginalLanguageString", NHibernateUtil.String)
                    .AddScalar("Localized", NHibernateUtil.String)
                    .AddScalar("English", NHibernateUtil.String)
                    .SetParameter("group", model.CurrentGroupPath)
                    .SetParameter("culture", culture);

Coordinator
Nov 4, 2011 at 8:41 PM

Yes, you can know that by noticing that it uses CreateSQLQuery.

Nov 5, 2011 at 3:10 PM

Thanks Bertrand. I am going to attempt to build this Module as an MVC 3 project in Visual Studio. I'm going to then try to drop it into Orchard and hope it works. Of course I'm going to adapt it for the Orchard enviorment by adding the proper Migration.cs files and anything else I need to make it run in Orchard. 

 

My question about the CreateSQLQuery method is this:

Is this method exposed my MVC? Is it exposed by some Orchard specific class? Or Hibernate Class? I want to make sure I can test it in visual Studio with the correct code.

Coordinator
Nov 7, 2011 at 8:09 PM

It comes from nHibernate.

Developer
Nov 9, 2011 at 10:26 PM

When I add a Sort Criteria 'Created Date' in a query, 'Ordered by Publication date' is displayed int the list of sort criterion even if the edit link is '/Orchard.Projections/SortCriterion/Edit/1?category=CommonPartRecord&type=CreatedUtc&sortCriterionId=1'.

And it seems that it sorts on Published Date.

Coordinator
Nov 9, 2011 at 10:33 PM

Yes, I hit that as well. But Sébastien doesn't believe me.

Coordinator
Nov 9, 2011 at 10:35 PM

ok, I trust you two now ... I can see it

Coordinator
Nov 9, 2011 at 10:54 PM

Fixed

Nov 10, 2011 at 9:08 PM

Trying to follow the N-1 relationship tutorial on the Orchard website. I keep getting a compile error at this method. It says it needs a return value? What would it be?

 

"Because the list of states is going to be relatively stable, I did not make them content items (although that would be entirely possible with just a little more work).

Instead, I'm populating the table with a list of states right in the migration code. The migration class has a reference to the state repository:"

private readonly IRepository _stateRepository;
[...]
public RelationSampleDataMigration(IRepository stateRepository) {
    _stateRepository = stateRepository;
}

Does it return stateRepository?

Coordinator
Nov 10, 2011 at 9:24 PM

If the feature is enabled, yes. But you're off-topic. Would you mind creating a new thread instead?

Nov 10, 2011 at 10:23 PM

Oops. Sorry about that. Will do.

Developer
Nov 15, 2011 at 12:12 PM

When I have created many queries and I reedit a projection widget, the 'For Query' property is not set : the query previously chosen is not reselected but it only happens with a (default Layout), not with a custom layout.

Developer
Dec 9, 2011 at 1:07 PM

Nice demo of projector here : http://www.youtube.com/watch?v=SP912eWoOoo

Thanks to Projector, would it be possible to make something like a faceted search or, more generally, a grouping of distinct links with an aggregated number ?

For example, I have a content type with tags or taxonomy and I want to display a projection of my contents with a filter that retrieve its value from the querystring.

Then, I'd like to add a widget that displays a list of distinct tags associated to the contents displayed and the number of occurences each tag.

Also, each tag would be a link that would add its value to the current url.

 

Another use would be to obtain a list of archives by Month for a content type that has a publication date (Even if, for a blog, the Blog Archives widget is more efficient because it recalculates the count by month each time a blogpost is published)

Developer
Dec 9, 2011 at 1:11 PM

Will it be possible to have an integration between Autoroute paths and Tokens to specify a filter retrieved from the url and to have friendly urls ?.

Developer
Dec 9, 2011 at 1:18 PM

Can we order on a 'Random' criterion to display a Random content (paged with 1 item)?

Dec 9, 2011 at 2:57 PM

Is it possible/would it be easy to be able to pass in arguments (filters) to the queries from the front-end? 

Dec 9, 2011 at 4:02 PM
Edited Dec 9, 2011 at 4:03 PM

hai2u: The video should show this in the questions at the end.

Dec 9, 2011 at 4:55 PM

agriffard:

"Another use would be to obtain a list of archives by Month for a content type that has a publication date (Even if, for a blog, the Blog Archives widget is more efficient because it recalculates the count by month each time a blogpost is published)"

"Will it be possible to have an integration between Autoroute paths and Tokens to specify a filter retrieved from the url and to have friendly urls ?."

This is an interesting question. Of course, you can use Alias (which is the underlying routing engine of Autoroute) to map friendly urls to any set of route values. But it's purely a string dictionary mapping, so you'd have to define an alias for every possible combination of query parameters that you wanted in your friendly urls. It might not be a good idea to create so many aliases, although we can discover this in profiling. It might just be easier with rewrites!

One thing I've been thinking about is allowing Alias some card of wildcard mapping so we can manage more complex routes - I think there's a way to do it with negligible overhead 

"Can we order on a 'Random' criterion to display a Random content (paged with 1 item)?"

As far as I know it didn't have this built in. But the system is extensible and you can build this as a new sort criteria plugin. See the following discussion where I mention some ways to perform a random select (towards the end): http://orchard.codeplex.com/discussions/274915

Dec 9, 2011 at 5:36 PM

Ah yes, sweet. Cheers Pete.

Developer
Dec 12, 2011 at 3:40 PM

If I have an Orchard instance with a previous version of 1.4, will it warn the user that the version of Orchard is not enough recent if he tries to install a module like Projector ?

Is there a checking between the current version of Orchard and the version specified in the Module.txt when the module are listed in the gallery, installed in admin or copied manually in /Modules ?

Dec 12, 2011 at 4:42 PM

Sebastien recently made that change, obviously it won't be in until 1.4, but from then on there will be such version checking.

Coordinator
Dec 13, 2011 at 8:33 PM

FYI, I have been reworking the whole dynamic query extensions added with Projector, because of a bug in NHibernate that prevents you from doing multiple inner joins on the same table. Kind of a bummer when you have a table like Property/Value records ... You can see it failing when I try to filter on a boolean field and order by a date field. It took me a few days to have something working again. But now it's ok, and I am currently creating unit tests for that.

Developer
Dec 13, 2011 at 10:53 PM

OK, the filters work better.

I just still have 2 strange cases :

1) I add an EnumerationField with some options on a content type and I set the value of the fields in contents : if I configure a query with a filter on that field to display the contents that 'contains' an option, nothing is displayed.

2) With a filter on a DateField, the sort operator says to set a Date with the format 'YYYY-MM-DD' but if I have a field with a value MM/DD/YYYY (Culture is En-US), it seems to sort as if the value compared was DD/MM/YYYY. 

 

Coordinator
Dec 13, 2011 at 11:03 PM

please open some bugs

Dec 28, 2011 at 1:52 PM

Hello Sebastien,

Does Projector work with Taxonomy fields?

Thanks,

-Venkat

Coordinator
Dec 28, 2011 at 9:00 PM

Yes

Feb 29, 2012 at 7:13 PM
Edited Feb 29, 2012 at 7:14 PM

Are there plans to ever implement joins in Projector?

In conjunction with a content reference field, it would open up more complex content relationships without the user having to code anything, similar to Drupal Views.