Search and complex queries

Hi!

I am wondering how people deal with advanced search pages when building an application or a web-site based on .netTiers. Let's say you have a Clients table, a ClientTypes Table, a Groups table and a Regions Table. A client has a Client Type, is located in a Region, and may be part of multiple Groups through a ClientsGroups n-n relationship table. Now, I want to be able to fetch clients based on  values from any of these tables. Normally, in order to perform a search based on various parameters, I would build the SQL query on the fly based on those parameters. And don't forget that I am exposing here only a subset of the real thing. It is in fact much more complex, so generating a mega Do-Everything view is not really a solution. I think there is a Query Builder in .netTiers, but if I start using this then I'll loose or bypass all the business-rules that I have programmed in my objects. So what is the elegant solution to this problem?

Best regards,

JF

 

are you willing to allow your users to select the variables that they want to search on? Of course I am using a View. So YMMV.

 

CREATE TABLE [dbo].[DoctorSearchCriteria](
 [id] [bigint] IDENTITY(1,1) NOT NULL,
 [Criteria] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
 [WhereClause] [varchar](150) COLLATE Latin1_General_CI_AI NULL,
 CONSTRAINT [PK_Doctor_SearchCriteria] PRIMARY KEY CLUSTERED
(
 [id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[DoctorSearchParameters](
 [CriteriaID] [bigint] NOT NULL,
 [ID] [bigint] NOT NULL,
 [Name] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
 CONSTRAINT [PK_DoctorSearchParameters] PRIMARY KEY CLUSTERED
(
 [ID] ASC,
 [CriteriaID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[DoctorSearchParameters]  WITH CHECK ADD  CONSTRAINT [FK_DoctorSearchParameters_DoctorSearchCriteria] FOREIGN KEY([CriteriaID])
REFERENCES [dbo].[DoctorSearchCriteria] ([id])
GO
ALTER TABLE [dbo].[DoctorSearchParameters] CHECK CONSTRAINT [FK_DoctorSearchParameters_DoctorSearchCriteria]

 

**CODE**
Public Shared Function [Get](ByVal searchcriteria As String, ByVal id As Integer) As Entities.VList(Of Entities.VwDoctorSearch)
  Dim rtn As New Entities.VList(Of Entities.VwDoctorSearch)
  Try
   rtn = [Get](CreateLikeParameters(ParseSearchString(searchcriteria), id))
  Catch ex As Exception
#If DEBUG Then
   MessageBox.Show(ex.ToString, String.Format("{0}.{1}", Utility.GetFileName, Utility.GetMethodName))
#End If
  End Try
  rtn.Sort("LastName asc,FirstName asc")
  Return rtn

 End Function
 Public Shared Function [Get](ByVal params As Data.Bases.VwDoctorSearchParameterBuilder) As Entities.VList(Of Entities.VwDoctorSearch)
  Dim s As New Services.VwDoctorSearchService

  Return s.Find(params.GetParameters)

 End Function

 Private Shared Function CreateEqualsParameters(ByVal criteria() As String, ByVal ID As Integer) As WavelengthIS.Referee.Data.Bases.VwDoctorSearchParameterBuilder
  Dim pb As New WavelengthIS.Referee.Data.Bases.VwDoctorSearchParameterBuilder(IGNORE_CASE)
  Dim params As Entities.TList(Of Entities.DoctorSearchParameters)

  params = DoctorSearchParametersController.GetByCriteriaID(ID)

  For i As Integer = 0 To params.Count - 1
   pb.AppendEquals(params(i).Name, criteria(i))
  Next
  Return pb
 End Function

 Private Shared Function CreateLikeParameters(ByVal criteria() As String, ByVal ID As Integer) As WavelengthIS.Referee.Data.Bases.VwDoctorSearchParameterBuilder
  Dim pb As New WavelengthIS.Referee.Data.Bases.VwDoctorSearchParameterBuilder(IGNORE_CASE)
  Dim params As Entities.TList(Of Entities.DoctorSearchParameters)

  params = DoctorSearchParametersController.GetByCriteriaID(ID)

  For i As Integer = 0 To params.Count - 1
   pb.Append(params(i).Name, String.Format("{0}%", criteria(i)))
  Next
  Return pb
 End Function
 Private Shared Function ParseSearchString(ByVal str As String) As String()

  Return str.Split(New [Char]() {" "c, ","c, "."c, ":"c}, StringSplitOptions.RemoveEmptyEntries)

 End Function

 

Hi Ecathell,

In fact, there is no real search "switchboard" in the site I am working on. The search criteria are determined by the path the web-surfer takes to navigate to what he wants to find. But it all comes to the same internally. In your case, the search is done on a string as far as I understand. In my case, it is done with specific parameters; in my example: a chosen region coming from a Regions table, a client type chosen from a Client Types table, and groups coming from a Groups table. The search may be performed according to only one or many of these parameters and more. Depending on the parameters chosen, I would not JOIN the same tables together if I were to perform the search in straight SQL.

 Cheers!

JF
 

Here is an example of what I would like to do, and I'm at lost trying to find a solution for this with .netTiers.

Let's says you have a Resorts database with the following tables:

Resorts (ResortID, ResortName, Stars, Country, ...)
Sports(SportID, SportName) : Tennis, Golf, SCUBA Diving, etc.
RoomTypes (RoomTypeID, NumberOfBeds, Shower, Bath) : 1 bed, 2beds, 4 beds, etc.
Activities (ActivityID, ActivityName, ActivityDescription) : Night Club, Theater, Dance Classes, etc.
Services (ServiceID, ServiceName, ServiceDescription) : Massage, Sauna, TV Room, Internet, etc.

And some N-N relationship tables to link the various items to the resorts:

ResortsRoomTypes (ResortID, RoomTypeID)
ResortsActivities (ResortID, ActivityID)
ResortsServices (ResortID, ServicesID)

Now, my problem is that with .netTiers, you cannot use JOINs. How would you create a query to find all resorts according to various criteria? Let's say, you want to be able to search Resorts by Sports and Services so that a user could search for Resorts offering Tennis, Golf and Internet?

A View won't do it here, because if we join all these tables in a View then the result set may contain multiple rows for each Resort.

Although, I really like .netTiers for many things, this problem is making me think that OO programming combined with Data Providers  instead of having direct access to the database may not be the Holly Grail in the end. For this part of the project, I am feeling that .netTiers is much more in the way than it is helping me.

Cheers!

JF

 

you may need to provide a multi path tiered approach below is my table settings for taxonomy. It is different from your criteria, as you have to choose one before the others, but it may give you some ideas. hth

 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TaxonomyEntityType]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[TaxonomyEntityType](
 [ID] [bigint] NOT NULL,
 [EntityType] [varchar](50) NULL,
 CONSTRAINT [PK_TaxonomyEntityType] PRIMARY KEY CLUSTERED
(
 [ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TaxonomyClassification]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[TaxonomyClassification](
 [TaxonomyTypeID] [varchar](5) NOT NULL,
 [ID] [varchar](5) NOT NULL,
 [Description] [varchar](50) NULL,
 [EntityTypeID] [bigint] NULL,
 CONSTRAINT [PK_TaxonomyClassification] PRIMARY KEY CLUSTERED
(
 [ID] ASC,
 [TaxonomyTypeID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Doctors]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Doctors](
 [ID] [bigint] IDENTITY(1,1) NOT NULL,
 [DisplayName] [varchar](210) NULL,
 [FirstName] [varchar](50) NULL,
 [LastName] [varchar](50) NULL,
 [MiddleName] [varchar](50) NULL,
 [Credentials] [varchar](50) NULL,
 [GroupID] [bigint] NOT NULL CONSTRAINT [DF_Doctors_GroupID]  DEFAULT ((1)),
 [Active] [bit] NOT NULL CONSTRAINT [DF_Doctors_Active]  DEFAULT ((1)),
 [AddDate] [datetime] NULL,
 [AddUserID] [varchar](50) NULL,
 [ChangeDate] [datetime] NULL,
 [ChangeUserID] [varchar](50) NULL,
 CONSTRAINT [PK_Doctors] PRIMARY KEY CLUSTERED
(
 [ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TaxonomyType]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[TaxonomyType](
 [ID] [varchar](5) NOT NULL,
 [Description] [varchar](50) NULL,
 [EntityTypeID] [bigint] NULL,
 CONSTRAINT [PK_TaxonomyType] PRIMARY KEY CLUSTERED
(
 [ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'TaxonomyType', N'COLUMN',N'ID'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Keane Taxonomy Type Code' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'TaxonomyType', @level2type=N'COLUMN',@level2name=N'ID'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'TaxonomyType', N'COLUMN',N'Description'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Taxonomy Description' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'TaxonomyType', @level2type=N'COLUMN',@level2name=N'Description'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'TaxonomyType', N'COLUMN',N'EntityTypeID'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Entity Type(person or non person)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'TaxonomyType', @level2type=N'COLUMN',@level2name=N'EntityTypeID'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TaxonomySpecialization]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[TaxonomySpecialization](
 [TaxonomyTypeID] [varchar](5) NOT NULL,
 [TaxonomyClassificationID] [varchar](5) NOT NULL,
 [ID] [varchar](5) NOT NULL,
 [Description] [varchar](50) NULL,
 [EntityTypeID] [bigint] NULL,
 [EducationRequired] [bit] NULL,
 CONSTRAINT [PK_TaxonomySpecialization] PRIMARY KEY CLUSTERED
(
 [TaxonomyTypeID] ASC,
 [TaxonomyClassificationID] ASC,
 [ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_TaxonomyClassification_TaxonomyEntityType]') AND parent_object_id = OBJECT_ID(N'[dbo].[TaxonomyClassification]'))
ALTER TABLE [dbo].[TaxonomyClassification]  WITH CHECK ADD  CONSTRAINT [FK_TaxonomyClassification_TaxonomyEntityType] FOREIGN KEY([EntityTypeID])
REFERENCES [dbo].[TaxonomyEntityType] ([ID])
GO
ALTER TABLE [dbo].[TaxonomyClassification] CHECK CONSTRAINT [FK_TaxonomyClassification_TaxonomyEntityType]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_TaxonomyClassification_TaxonomyType]') AND parent_object_id = OBJECT_ID(N'[dbo].[TaxonomyClassification]'))
ALTER TABLE [dbo].[TaxonomyClassification]  WITH CHECK ADD  CONSTRAINT [FK_TaxonomyClassification_TaxonomyType] FOREIGN KEY([TaxonomyTypeID])
REFERENCES [dbo].[TaxonomyType] ([ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[TaxonomyClassification] CHECK CONSTRAINT [FK_TaxonomyClassification_TaxonomyType]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Doctors_DoctorGroups]') AND parent_object_id = OBJECT_ID(N'[dbo].[Doctors]'))
ALTER TABLE [dbo].[Doctors]  WITH CHECK ADD  CONSTRAINT [FK_Doctors_DoctorGroups] FOREIGN KEY([GroupID])
REFERENCES [dbo].[DoctorGroups] ([ID])
GO
ALTER TABLE [dbo].[Doctors] CHECK CONSTRAINT [FK_Doctors_DoctorGroups]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_TaxonomyType_TaxonomyEntityType]') AND parent_object_id = OBJECT_ID(N'[dbo].[TaxonomyType]'))
ALTER TABLE [dbo].[TaxonomyType]  WITH CHECK ADD  CONSTRAINT [FK_TaxonomyType_TaxonomyEntityType] FOREIGN KEY([EntityTypeID])
REFERENCES [dbo].[TaxonomyEntityType] ([ID])
GO
ALTER TABLE [dbo].[TaxonomyType] CHECK CONSTRAINT [FK_TaxonomyType_TaxonomyEntityType]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_TaxonomySpecialization_TaxonomyClassification]') AND parent_object_id = OBJECT_ID(N'[dbo].[TaxonomySpecialization]'))
ALTER TABLE [dbo].[TaxonomySpecialization]  WITH CHECK ADD  CONSTRAINT [FK_TaxonomySpecialization_TaxonomyClassification] FOREIGN KEY([TaxonomyClassificationID], [TaxonomyTypeID])
REFERENCES [dbo].[TaxonomyClassification] ([ID], [TaxonomyTypeID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[TaxonomySpecialization] CHECK CONSTRAINT [FK_TaxonomySpecialization_TaxonomyClassification]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_TaxonomySpecialization_TaxonomyEntityType]') AND parent_object_id = OBJECT_ID(N'[dbo].[TaxonomySpecialization]'))
ALTER TABLE [dbo].[TaxonomySpecialization]  WITH CHECK ADD  CONSTRAINT [FK_TaxonomySpecialization_TaxonomyEntityType] FOREIGN KEY([EntityTypeID])
REFERENCES [dbo].[TaxonomyEntityType] ([ID])
GO
ALTER TABLE [dbo].[TaxonomySpecialization] CHECK CONSTRAINT [FK_TaxonomySpecialization_TaxonomyEntityType]

Hi velum,

you can use a custom stored procedure for joins outside of the scope of your entities. we write a lot of those in a large application I am working on. we also use some dynamic sql in our stored procs to allow us to have flexible order by clauses.

if you check the templates documentation, if your stored proc. returns the same columns as the view or table they are bound to (through their naming convention), the generated methods will return collections of objects.

A simple example is having 3 tables:

<SUser> - <SUserApp> - <SApp>

 If you want return a collection of SUser based on a filter applied to SApp, your stored proc needs to join these tables, apply your where filter on the fields from SApp and then insert an identifier for each record in a temp table to then apply paging (if needed). In your case, if your join returns more than one record, just return the distinct list of ids of Resorts after joining to room types (through the many to many table) and applying your search.

CREATE PROCEDURE [dbo].[cstm_SUser_GetBySAppId]

@SAppId int

, @WhereClause nvarchar(2000)

, @OrderBy nvarchar(2000)

, @PageIndex int

, @PageSize int

, @RowCount int OUTPUT

AS

BEGIN

DECLARE @PageLowerBound int

DECLARE @PageUpperBound int

DECLARE @RowsToReturn int

DECLARE @PagingTable

Table (

[IndexId] int IDENTITY,

[sUserId] int

)

-- First set the rowcount

SET @RowsToReturn = @PageSize * (@PageIndex + 1)

--SET ROWCOUNT @RowsToReturn

-- Set the page bounds

SET @PageLowerBound = @PageSize * @PageIndex

SET @PageUpperBound = @PageLowerBound + @PageSize

-- Insert into the temp table

declare @SQL as nvarchar(3500)

-- Create join to apply filter

IF ISNULL(@InverseInclude, 0) <> 1

BEGIN

SET @SQL = '

SELECT [sUserApp].[sUserId]

FROM [sUserApp]

INNER JOIN [sUser]

ON [sUser].[sUserId] = [sUserApp].[sUserId]

WHERE sAppId = ' + ltrim(str(@SAppId))

END

-- append the dynamic where and order by clauses

IF LEN(@WhereClause) > 0

BEGIN

SET @SQL = @SQL + ' AND ( ' + @WhereClause + ' )'

END

IF LEN(@OrderBy) > 0

BEGIN

SET @SQL = @SQL + ' ORDER BY ' + @OrderBy

END

-- Populate the temp table

INSERT INTO @PagingTable (sUserId)

exec sp_executesql @SQL

-- Return total count

SELECT @RowCount = @@ROWCOUNT

-- Set upper bound to row count if the passed

-- page size was 0 or less

IF @PageSize <= 0

BEGIN

SET @PageUpperBound = @RowCount

END

-- Return paged results

SELECT O.*

FROM

dbo.[SUser] O

INNER JOIN @PagingTable PageIndex

ON PageIndex.[sUserId] = O.[sUserId]

WHERE PageIndex.IndexId > @PageLowerBound

AND PageIndex.IndexId <= @PageUpperBound

ORDER BY

PageIndex.IndexId

END