Search Results

Search found 23613 results on 945 pages for 'query parameters'.

Page 18/945 | < Previous Page | 14 15 16 17 18 19 20 21 22 23 24 25  | Next Page >

  • Get count matches in query on large table very slow

    - by Roy Roes
    I have a mysql table "items" with 2 integer fields: seid and tiid The table has about 35000000 records, so it's very large. seid tiid ----------- 1 1 2 2 2 3 2 4 3 4 4 1 4 2 The table has a primary key on both fields, an index on seid and an index on tiid. Someone types in 1 or more tiid values and now I would like to get the seid with most results. For example when someone types 1,2,3, I would like to get seid 2 and 4 as result. They both have 2 matches on the tiid values. My query so far: SELECT COUNT(*) as c, seid FROM items WHERE tiid IN (1,2,3) GROUP BY seid HAVING c = (SELECT COUNT(*) as c, seid FROM items WHERE tiid IN (1,2,3) GROUP BY seid ORDER BY c DESC LIMIT 1) But this query is extremly slow, because of the large table. Does anyone know how to construct a better query for this purpose?

    Read the article

  • MySQL Query, Date Range From "Blacklist"

    - by erbaker
    I have 2 databases. One is properties and the other is dates. In dates I have associated the land_id and a date (In YYYYMMDD format) which means that the date is not available. I need to formulate a query that a user can specify a start and end date, and then choose a property for which dates are available (not in the date database). How do airline and hotel websites do this kind of logic? I was thinking about taking the date range and picking all days in between and doing a query where the dates do not match and ordering it by number of results, but I can see how that could easily turn into an intense query. CREATE TABLE IF NOT EXISTS `dates` ( `id` int(11) NOT NULL AUTO_INCREMENT, `land_id` int(11) NOT NULL, `date` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=44 ; -- -- Dumping data for table `dates` -- INSERT INTO `dates` (`id`, `land_id`, `date`) VALUES (43, 1, '20100526'), (39, 1, '20100522'), (40, 1, '20100523'), (41, 1, '20100521'), (42, 1, '20100525');

    Read the article

  • Query with multiple IN-statements but without the cartesian product

    - by Janne
    How could I make this kind of query e.g. in MySQL SELECT * FROM Table t WHERE t.a IN (1,2,3) AND t.b IN (4,5,6) AND t.c IN (7,8,9) ... so that the result would contain only the three rows: t.a|t.b|t.c ---+---+--- 1 | 4 | 7 2 | 5 | 8 3 | 6 | 9 The above query of course returns all the combinations of the values in the IN clauses but I would like to get just the ones where the first elements of each tuple match, second elements of each tuple match and so on. Is there any efficient way to do this? By the way is there some common term for this kind of query or concept? I'm having hard time coming up with the question's title because I can't put this into words..

    Read the article

  • Simple PHP query question: LIKE

    - by pg
    When I replace $ordering = "apples, bananas, cranberries, grapes"; with $ordering = "apples, bananas, grapes"; I no longer want cranberries to be returned by my query, which I've written out like this: $query = "SELECT * from dbname where FruitName LIKE '$ordering'"; Of Course this doesn't work, because I used LIKE wrong. I've read through various manuals that describe how to use LIKE and it doesn't quite make sense to me. If I change the end of the db to "LIKE "apples"" that works for limiting it to just apples. Do I have to explode the ordering on the ", " or is there a way to do this in the query?

    Read the article

  • MySQL Multiple "AND" Query

    - by Mark J
    I have a table with 2 columns (see below). A member can have multiple responses to a question RESPONSES --------- member_id INT response_id INT SAMPLE DATA member_id -- response_id 1 -- 3 1 -- 5 2 -- 1 2 -- 5 2 -- 9 3 -- 1 3 -- 5 3 -- 6 What I need to do is query the table for member that meet ALL response criteria. For example I need to select all members that have a response_id of 1 AND 5. I am using the following query: SELECT DISTINCT member_id FROM responses WHERE response_id = 1 AND response_id = 5. I would expect to get back member_id's 2,3. However I am getting nothing returned. I used EXPLAIN and it shows there is an error in my where query. What am I doing wrong? Also, is there a function similar to IN where all the criteria must be met in order to return true? Thanks for your help.

    Read the article

  • Getting Query Parameters in Javascript

    - by PhubarBaz
    I find myself needing to get query parameters that are passed into a web app on the URL quite often. At first I wrote a function that creates an associative array (aka object) with all of the parameters as keys and returns it. But then I was looking at the revealing module pattern, a nice javascript design pattern designed to hide private functions, and came up with a way to do this without even calling a function. What I came up with was this nice little object that automatically initializes itself into the same associative array that the function call did previously. // Creates associative array (object) of query params var QueryParameters = (function() {     var result = {};     if (window.location.search)     {         // split up the query string and store in an associative array         var params = window.location.search.slice(1).split("&");         for (var i = 0; i < params.length; i++)         {             var tmp = params[i].split("=");             result[tmp[0]] = unescape(tmp[1]);         }     }     return result; }()); Now all you have to do to get the query parameters is just reference them from the QueryParameters object. There is no need to create a new object or call any function to initialize it. var debug = (QueryParameters.debug === "true"); or if (QueryParameters["debug"]) doSomeDebugging(); or loop through all of the parameters. for (var param in QueryParameters) var value = QueryParameters[param]; Hope you find this object useful.

    Read the article

  • MYSQL - Selecting a specific date range to get "current" popular screensavers.

    - by Joe
    Let's say I have a screensaver website. I want to display the CURRENT top 100 screensavers on the front page of the website. What I mean is, "RECENT" top 100 screensavers. What would be an example query to do this? My current one is: SELECT * FROM tbl_screensavers WHERE WEEK(tbl_screensavers.DateAdded) = WEEK('".date("Y-m-d H:i:s",strtotime("-1 week"))."') ORDER BY tbl_screensavers.ViewsCount, tbl_screensavers.DateAdded This will select the most viewed ("tbl_screensavers.ViewsCount") screensavers that were added ("tbl_screensavers.DateAdded") in the last week. However, in some cases there are no screensavers, or less than 100 screensavers, submitted in that week. So, how can I perform a query which would select "RECENT" top 100 screensavers? Hopefully you have an idea of what I'm try to accomplish when I say "RECENT" or "CURRENT" top screensavers. -- aka. the most viewed, recently - not the most viewed, all-time.

    Read the article

  • Understanding #DAX Query Plans for #powerpivot and #tabular

    - by Marco Russo (SQLBI)
    Alberto Ferrari wrote a very interesting white paper about DAX query plans. We published it on a page where we'll gather articles and tools about DAX query plans: http://www.sqlbi.com/topics/query-plans/I reviewed the paper and this is the result of many months of study - we know that we just scratched the surface of this topic, also because we still don't have enough information about internal behavior of many of the operators contained in a query plan. However, by reading the paper you will start reading a query plan and you will understand how it works the optimization found by Chris Webb one month ago to the events-in-progress scenario. The white paper also contains a more optimized query (10 time faster), even if the performance depends on data distribution and the best choice really depends on the data you have. Now you should be curious enough to read the paper until the end, because the more optimized query is the last example in the paper!

    Read the article

  • Why is doing a top(1) on an indexed column in SQL Server slow?

    - by reinier
    I'm puzzled by the following. I have a DB with around 10 million rows, and (among other indices) on 1 column (campaignid_int) is an index. Now I have 700k rows where the campaignid is indeed 3835 For all these rows, the connectionid is the same. I just want to find out this connectionid. use messaging_db; SELECT TOP (1) connectionid FROM outgoing_messages WITH (NOLOCK) WHERE (campaignid_int = 3835) Now this query takes approx 30 seconds to perform! I (with my small db knowledge) would expect that it would take any of the rows, and return me that connectionid If I test this same query for a campaign which only has 1 entry, it goes really fast. So the index works. How would I tackle this and why does this not work? edit: estimated execution plan: select (0%) - top (0%) - clustered index scan (100%)

    Read the article

  • Why is doing a top(1) on an indexed column in mssql slow?

    - by reinier
    I'm puzzled by the following. I have a DB with around 10 million rows, and (among other indices) on 1 column is an index. Now I have 700k rows where the campaignid is indeed 3835 For all these rows, the connectionid is the same. I just want to find out this connectionid. use messaging_db; SELECT TOP (1) connectionid FROM outgoing_messages WITH (NOLOCK) WHERE (campaignid_int = 3835) Now this query takes approx 30 seconds to perform! I (with my small db knowledge) would expect that it would take any of the rows, and return me that connectionid If I test this same query for a campaign which only has 1 entry, it goes really fast. So the index works. How would I tackle this and why does this not work?

    Read the article

  • Suggestion for setting web application parameters

    - by user40730
    I'm creating a web application on GWT. I'm using MVP pattern with activities and places. I have a xml config file containing some parameters to be used by the application. Content of this xml file is sent to the client using HttpRequest; I'm using a singleton class to hold the information from the xml file. Right now, the application is getting the data when the user starts the application in the home page, that is working well. Now, since I'm using activities and places, a user can bookmark a page and starts the application in any other page (Place). And here comes the problem: Since I'm using some of the information from the xml file to set some ui widgets, I have to check if the xml config file was read and the application already has the parameters (I do this by checking the singleton class). But the xml file is read by using an HttpRequest, so I got errors 'cause the application needs some parameters to initialize some ui widgets, but these parameters aren't ready on time. I was thinking on using an synchronous request to fix the problem, but it seems complicated and not recommendable to do that. So, I'd like to hear some other suggestions. Thanks.

    Read the article

  • Insert new row with data computed from other rows

    - by Tyler McHenry
    Suppose I have a MySQL table called MyTable, that looks like this: +----+------+-------+ | Id | Type | Value | +----+------+-------+ | 0 | A | 1 | | 0 | B | 1 | | 1 | A | 2 | | 1 | B | 3 | | 2 | A | 5 | | 2 | B | 8 | +----+------+-------+ And, for each Id, I want to insert a new row with type C whose Value is the sum of the type A and B values for the rows of the same Id. The primary key on this table is (Id, Type), so there's no question of duplication of Id,Type pairs. I can create the rows I want with this query: SELECT MyTable_A.Id AS Id, 'C' AS Type, (A_Val + B_Val) AS Value FROM (SELECT Id, Value AS A_Val FROM MyTable WHERE Type='A') AS MyTable_A JOIN (SELECT Id, Value AS B_Val FROM MyTable WHERE Type='B') AS MyTable_B ON MyTable_A.Id = MyTable_B.Id Giving: +----+------+-------+ | Id | Type | Value | +----+------+-------+ | 0 | C | 2 | | 1 | C | 5 | | 2 | C | 13 | +----+------+-------+ But the question is: How do I use this result to insert the generated type-C rows into MyTable? Is there a relatively simple way to do this with a query, or do I need to write a stored procedure? And if the latter, guidance would be helpful, as I'm not too well versed in them.

    Read the article

  • Optional Parameters and Named Arguments in C# 4 (and a cool scenario w/ ASP.NET MVC 2)

    - by ScottGu
    [In addition to blogging, I am also now using Twitter for quick updates and to share links. Follow me at: twitter.com/scottgu] This is the seventeenth in a series of blog posts I’m doing on the upcoming VS 2010 and .NET 4 release. Today’s post covers two new language feature being added to C# 4.0 – optional parameters and named arguments – as well as a cool way you can take advantage of optional parameters (both in VB and C#) with ASP.NET MVC 2. Optional Parameters in C# 4.0 C# 4.0 now supports using optional parameters with methods, constructors, and indexers (note: VB has supported optional parameters for awhile). Parameters are optional when a default value is specified as part of a declaration.  For example, the method below takes two parameters – a “category” string parameter, and a “pageIndex” integer parameter.  The “pageIndex” parameter has a default value of 0, and as such is an optional parameter: When calling the above method we can explicitly pass two parameters to it: Or we can omit passing the second optional parameter – in which case the default value of 0 will be passed:   Note that VS 2010’s Intellisense indicates when a parameter is optional, as well as what its default value is when statement completion is displayed: Named Arguments and Optional Parameters in C# 4.0 C# 4.0 also now supports the concept of “named arguments”.  This allows you to explicitly name an argument you are passing to a method – instead of just identifying it by argument position.  For example, I could write the code below to explicitly identify the second argument passed to the GetProductsByCategory method by name (making its usage a little more explicit): Named arguments come in very useful when a method supports multiple optional parameters, and you want to specify which arguments you are passing.  For example, below we have a method DoSomething that takes two optional parameters: We could use named arguments to call the above method in any of the below ways: Because both parameters are optional, in cases where only one (or zero) parameters is specified then the default value for any non-specified arguments is passed. ASP.NET MVC 2 and Optional Parameters One nice usage scenario where we can now take advantage of the optional parameter support of VB and C# is with ASP.NET MVC 2’s input binding support to Action methods on Controller classes. For example, consider a scenario where we want to map URLs like “Products/Browse/Beverages” or “Products/Browse/Deserts” to a controller action method.  We could do this by writing a URL routing rule that maps the URLs to a method like so: We could then optionally use a “page” querystring value to indicate whether or not the results displayed by the Browse method should be paged – and if so which page of the results should be displayed.  For example: /Products/Browse/Beverages?page=2. With ASP.NET MVC 1 you would typically handle this scenario by adding a “page” parameter to the action method and make it a nullable int (which means it will be null if the “page” querystring value is not present).  You could then write code like below to convert the nullable int to an int – and assign it a default value if it was not present in the querystring: With ASP.NET MVC 2 you can now take advantage of the optional parameter support in VB and C# to express this behavior more concisely and clearly.  Simply declare the action method parameter as an optional parameter with a default value: C# VB If the “page” value is present in the querystring (e.g. /Products/Browse/Beverages?page=22) then it will be passed to the action method as an integer.  If the “page” value is not in the querystring (e.g. /Products/Browse/Beverages) then the default value of 0 will be passed to the action method.  This makes the code a little more concise and readable. Summary There are a bunch of great new language features coming to both C# and VB with VS 2010.  The above two features (optional parameters and named parameters) are but two of them.  I’ll blog about more in the weeks and months ahead. If you are looking for a good book that summarizes all the language features in C# (including C# 4.0), as well provides a nice summary of the core .NET class libraries, you might also want to check out the newly released C# 4.0 in a Nutshell book from O’Reilly: It does a very nice job of packing a lot of content in an easy to search and find samples format. Hope this helps, Scott

    Read the article

  • Using Table-Valued Parameters in SQL Server

    - by Jesse
    I work with stored procedures in SQL Server pretty frequently and have often found myself with a need to pass in a list of values at run-time. Quite often this list contains a set of ids on which the stored procedure needs to operate the size and contents of which are not known at design time. In the past I’ve taken the collection of ids (which are usually integers), converted them to a string representation where each value is separated by a comma and passed that string into a VARCHAR parameter of a stored procedure. The body of the stored procedure would then need to parse that string into a table variable which could be easily consumed with set-based logic within the rest of the stored procedure. This approach works pretty well but the VARCHAR variable has always felt like an un-wanted “middle man” in this scenario. Of course, I could use a BULK INSERT operation to load the list of ids into a temporary table that the stored procedure could use, but that approach seems heavy-handed in situations where the list of values is usually going to contain only a few dozen values. Fortunately SQL Server 2008 introduced the concept of table-valued parameters which effectively eliminates the need for the clumsy middle man VARCHAR parameter. Example: Customer Transaction Summary Report Let’s say we have a report that can summarize the the transactions that we’ve conducted with customers over a period of time. The report returns a pretty simple dataset containing one row per customer with some key metrics about how much business that customer has conducted over the date range for which the report is being run. Sometimes the report is run for a single customer, sometimes it’s run for all customers, and sometimes it’s run for a handful of customers (i.e. a salesman runs it for the customers that fall into his sales territory). This report can be invoked from a website on-demand, or it can be scheduled for periodic delivery to certain users via SQL Server Reporting Services. Because the report can be created from different places and the query to generate the report is complex it’s been packed into a stored procedure that accepts three parameters: @startDate – The beginning of the date range for which the report should be run. @endDate – The end of the date range for which the report should be run. @customerIds – The customer Ids for which the report should be run. Obviously, the @startDate and @endDate parameters are DATETIME variables. The @customerIds parameter, however, needs to contain a list of the identity values (primary key) from the Customers table representing the customers that were selected for this particular run of the report. In prior versions of SQL Server we might have made this parameter a VARCHAR variable, but with SQL Server 2008 we can make it into a table-valued parameter. Defining And Using The Table Type In order to use a table-valued parameter, we first need to tell SQL Server about what the table will look like. We do this by creating a user defined type. For the purposes of this stored procedure we need a very simple type to model a table variable with a single integer column. We can create a generic type called ‘IntegerListTableType’ like this: CREATE TYPE IntegerListTableType AS TABLE (Value INT NOT NULL) Once defined, we can use this new type to define the @customerIds parameter in the signature of our stored procedure. The parameter list for the stored procedure definition might look like: 1: CREATE PROCEDURE dbo.rpt_CustomerTransactionSummary 2: @starDate datetime, 3: @endDate datetime, 4: @customerIds IntegerListTableTableType READONLY   Note the ‘READONLY’ statement following the declaration of the @customerIds parameter. SQL Server requires any table-valued parameter be marked as ‘READONLY’ and no DML (INSERT/UPDATE/DELETE) statements can be performed on a table-valued parameter within the routine in which it’s used. Aside from the DML restriction, however, you can do pretty much anything with a table-valued parameter as you could with a normal TABLE variable. With the user defined type and stored procedure defined as above, we could invoke like this: 1: DECLARE @cusomterIdList IntegerListTableType 2: INSERT @customerIdList VALUES (1) 3: INSERT @customerIdList VALUES (2) 4: INSERT @customerIdList VALUES (3) 5:  6: EXEC dbo.rpt_CustomerTransationSummary 7: @startDate = '2012-05-01', 8: @endDate = '2012-06-01' 9: @customerIds = @customerIdList   Note that we can simply declare a variable of type ‘IntegerListTableType’ just like any other normal variable and insert values into it just like a TABLE variable. We could also populate the variable with a SELECT … INTO or INSERT … SELECT statement if desired. Using The Table-Valued Parameter With ADO .NET Invoking a stored procedure with a table-valued parameter from ADO .NET is as simple as building a DataTable and passing it in as the Value of a SqlParameter. Here’s some example code for how we would construct the SqlParameter for the @customerIds parameter in our stored procedure: 1: var customerIdsParameter = new SqlParameter(); 2: customerIdParameter.Direction = ParameterDirection.Input; 3: customerIdParameter.TypeName = "IntegerListTableType"; 4: customerIdParameter.Value = selectedCustomerIds.ToIntegerListDataTable("Value");   All we’re doing here is new’ing up an instance of SqlParameter, setting the pamameters direction, specifying the name of the User Defined Type that this parameter uses, and setting its value. We’re assuming here that we have an IEnumerable<int> variable called ‘selectedCustomerIds’ containing all of the customer Ids for which the report should be run. The ‘ToIntegerListDataTable’ method is an extension method of the IEnumerable<int> type that looks like this: 1: public static DataTable ToIntegerListDataTable(this IEnumerable<int> intValues, string columnName) 2: { 3: var intergerListDataTable = new DataTable(); 4: intergerListDataTable.Columns.Add(columnName); 5: foreach(var intValue in intValues) 6: { 7: var nextRow = intergerListDataTable.NewRow(); 8: nextRow[columnName] = intValue; 9: intergerListDataTable.Rows.Add(nextRow); 10: } 11:  12: return intergerListDataTable; 13: }   Since the ‘IntegerListTableType’ has a single int column called ‘Value’, we pass that in for the ‘columnName’ parameter to the extension method. The method creates a new single-columned DataTable using the provided column name then iterates over the items in the IEnumerable<int> instance adding one row for each value. We can then use this SqlParameter instance when invoking the stored procedure just like we would use any other parameter. Advanced Functionality Using passing a list of integers into a stored procedure is a very simple usage scenario for the table-valued parameters feature, but I’ve found that it covers the majority of situations where I’ve needed to pass a collection of data for use in a query at run-time. I should note that BULK INSERT feature still makes sense for passing large amounts of data to SQL Server for processing. MSDN seems to suggest that 1000 rows of data is the tipping point where the overhead of a BULK INSERT operation can pay dividends. I should also note here that table-valued parameters can be used to deal with more complex data structures than single-columned tables of integers. A User Defined Type that backs a table-valued parameter can use things like identities and computed columns. That said, using some of these more advanced features might require the use the SqlDataRecord and SqlMetaData classes instead of a simple DataTable. Erland Sommarskog has a great article on his website that describes when and how to use these classes for table-valued parameters. What About Reporting Services? Earlier in the post I referenced the fact that our example stored procedure would be called from both a web application and a SQL Server Reporting Services report. Unfortunately, using table-valued parameters from SSRS reports can be a bit tricky and warrants its own blog post which I’ll be putting together and posting sometime in the near future.

    Read the article

  • hierachical query to return final row

    - by jeff
    I have a hierarchical query that doesn't return an expected row (employee badge = 444). TABLE: hr_data badge fname supervisor_badge 111 Jeff 222 222 Joe 333 333 John 444 444 Tom 444 SQL: SELECT CONNECT_BY_ISCYCLE As IC, badge, fname, supervisor_badge FROM hr_data START WITH badge = '111' CONNECT BY NOCYCLE badge = PRIOR supervisor_badge What is Returned: IC badge fname supervisor_badge 0 111 Jeff 222 0 222 Joe 333 1 333 John 444 What is Expected: IC badge fname supervisor_badge 0 111 Jeff 222 0 222 Joe 333 **0** 333 John 444 **1** 444 Tom 444 How can I get this query to return the employee Tom and then stop?

    Read the article

  • Using Treelist Values to Query a Sitecore Item

    - by kirk.burleson
    I have an item named All Recipes that contains recipes named R1, R2, and R3. I have another item named My Recipes that has a treelist field named Recipes and it contains selected values R2 and R3 from the All Recipes item. The query I'm trying to write is for the Items field of an RSS Feed. What is the query syntax to show the items in All Recipes that appear in the Recipes field of My Recipes?

    Read the article

  • Problemwit sql query

    - by phenevo
    Hi, I've got query: INSERT INTO [Tasks] ([LoginName] ,[Type] ,[Filter] ,[Dictionary] ,[Description]) Select N'Anonymous',4,'SomeTable.targetcode in (select Code from cities where countrycode in ('TN')) and SomeTable.SomeValue in ('13','15')',3,N'Cities from tunis' Union All ... [Dictionary] is a part of query that i need to function on my server. I get: Incorrect syntax near ')) and SomeTable.SomeValue in (13,15)'.

    Read the article

  • MySQL query from subquery not working

    - by James Goodwin
    I am trying to return a number based on the count of results from a table and to avoid having to count the results twice in the IF statement I am using a subquery. However I get a syntax error when trying to run the query, the subquery I have tested by itself runs fine. Any ideas what is wrong with the query? The syntax looks correct to me SELECT IF(daily_count>8000,0,IF(daily_count>6000,1,2)) FROM ( SELECT count(*) as daily_count FROM orders201003 WHERE DATE_FORMAT(date_sub(curdate(), INTERVAL 1 DAY),"%d-%m-%y") = DATE_FORMAT(reqDate,"%d-%m-%y") ) q

    Read the article

  • Using Hibernate to do a query involving two tables

    - by Nathan Spears
    I'm inexperienced with sql in general, so using Hibernate is like looking for an answer before I know exactly what the question is. Please feel free to correct any misunderstandings I have. I am on a project where I have to use Hibernate. Most of what I am doing is pretty basic and I could copy and modify. Now I would like to do something different and I'm not sure how configuration and syntax need to come together. Let's say I have two tables. Table A has two (relevant) columns, user GUID and manager GUID. Obviously managers can have more than one user under them, so queries on manager can return more than one row. Additionally, a manager can be managing the same user on multiple projects, so the same user can be returned multiple times for the same manager query. Table B has two columns, user GUID and user full name. One-to-one mapping there. I want to do a query on manager GUID from Table A, group them by unique User GUID (so the same User isn't in the results twice), then return those users' full names from Table B. I could do this in sql without too much trouble but I want to use Hibernate so I don't have to parse the sql results by hand. That's one of the points of using Hibernate, isn't it? Right now I have Hibernate mappings that map each column in Table A to a field (well the get/set methods I guess) in a DAO object that I wrote just to hold that Table's data. I could also use the Hibernate DAOs I have to access each table separately and do each of the things I mentioned above in separate steps, but that would be less efficient (I assume) that doing one query. I wrote a Service object to hold the data that gets returned from the query (my example is simplified - I'm going to keep some other data from Table A and get multiple columns from Table B) but I'm at a loss for how to write a DAO that can do the join, or use the DAOs I have to do the join. FYI, here is a sample of my hibernate config file (simplified to match my example): <hibernate-mapping package="com.my.dao"> <class name="TableA" table="table_a"> <id name="pkIndex" column="pk_index" /> <property name="userGuid" column="user_guid" /> <property name="managerGuid" column="manager_guid" /> </class> </hibernate-mapping> So then I have a DAOImplementation class that does queries and returns lists like public List<TableA> findByHQL(String hql, Map<String, String> params) etc. I'm not sure how "best practice" that is either.

    Read the article

  • Convert SQL query to Ruby help

    - by Verloren
    Hey all, I need to query my database table to find which employee has the most support tickets related to them. I can do this just fine using this MySQL query: SELECT employee_id, COUNT(id) AS number_of_tickets FROM tickets GROUP BY employee_id ORDER BY number_of_tickets DESC LIMIT 1; How would write this in Ruby-on-Rails? Thanks very much for your assistance. I use Ruby version 1.8.6, Rails version 2.2.2 and MySQL Server version 5.0.

    Read the article

  • Issues with Rails 3.1 API with Query String to Create action on Mac OSX Mountain Lion

    - by hjaved
    Hi I've been stuck on this problem for a while and would appreciate your help. I'm writing an API to allow an external source like a Browser Query String or a smartphone to enter some model User info in a form and hit the User create action to write the data to the db. Please tell me what I'm doing wrong with the code below. I've also observed that if I have code like @user = User.new(params[:user]), that this approach only works when a user enters their data within the form. And that if I have code such as @user = User.new( name: params[:name], location: params[:location], password = params[:password], email: params[:email]), that this code ONLY works for a Query string entry, but NOT both Query string AND regular form submission. Why is that and how can I write the code above in the Users Controller Create action, so that it takes care of both situations? URL used: localhost:3000/users/create?name=John&&[email protected]&&password=secret&&location=SanFrancisco&date=06122012 The date is of type string but it doesn't show up in the database. Why? Everything else does. UsersController.rb def create @user = User.new(params[:user]) if @user.save session[:uid] = @user.id redirect_to thanks_path, notice: "Welcome #{@user.name}!" else redirect_to root_path end end New User Form: <%=u.text_field :name, placeholder: "Name"%><br> <%=u.text_field :email, placeholder: "Email"%><br> <%=u.password_field :password, placeholder: "Password"%><br> <%=u.text_field :location, placeholder: "City"%><br> <%=u.text_field :date, placeholder: "Date"%><br> <%if params[:partner_id]%> <%=u.hidden_field :partner_id, value: params[:partner_id]%> <%end%> <button class="btn btn-large btn-primary">Enter</button> I also tried to create a separate method called remotecreate for User creation for something other than a regular web form. I entered remotecreate in the Query string but it didn't work. def remotecreate @user = User.create(name: params[:name], email: params[:email], password: params[:password], location: params[:location], date: params[:date]) if @user.save session[:uid] = @user.id redirect_to thanks_path, notice: "Welcome #{@user.name}" else redirect_to root_path end end Thanks!

    Read the article

  • Any way to speed up this hierarchical query?

    - by RenderIn
    I've got a serious performance problem with a hierarchical query that I can't seem to fix. I am modeling several organization charts in my database, each representing a virtual organization within our company. For example, we have several temporary committees that are created from time to time and there may be a Committee Organizer role at the top of this virtual hierarchy, with several people assigned to the Committee Member role beneath the organizer. Some of our virtual organizations have many levels and several branches at each level. I have a single table in which I represent all the role assignments. i.e. a ROLE_ID column and a PARENT_ROLE_ID column which is a foreign key to the ROLE_ID column. For each assignment we also store as a column the location in the company where this person has the assignment. For example, the Committee Organizer would have a company-level/ CEO assignment, while the committee members would have department-level assignments such as ACCOUNTING, MARKETING, etc. So to model the organizer/member relationship for two individuals we would have: ROLE_ID = 4 PARENT_ROLE_ID = NULL EMPLOYEE_NUMBER = 213423 COMPANY_LOCATION = CEO ROLE_ID = 5 PARENT_ROLE_ID = 4 EMPLOYEE_NUMBER = 838221 COMPANY_LOCATION = ACCOUNTING Here's where things get tricky. I have an application that every person in the organization can log in to. When they log in they should be able to view all the virtual organizations in our company. e.g. the committee members should be able to see the committee organizer and vice-versa. However, only the committee organizer should be able to edit the committee members. The difficulty is in determining whether an individual (who can have multiple role assignments) has edit access for each other assignment. While this seems simple in the example, consider a virtual organization in which we have President at the top, 5 departments directly beneath him, 2 subdepartments below each department. We only want people in the Accounting department to be able to edit individuals in the subdepartments belonging to the Accounting department. They should not have edit access to anybody in the Marketing department or its subdepartments. To determine edit access when a user views a virtual organization in our company I run a query that executes two inline views: A) Hierarchically query for all assignments in this virtual organization and using SYS_CONNECT_BY_PATH to store the entire path to each user/role/company_location and B) Hierarchically retrieve all the assignments the individual logged in has and using the SYS_CONNECT_BY_PATH to store the entire path to each of these assignments. The result of the query is all the records from A) plus a boolean determined by joining with B) which flags whether the logged in user has edit access for each record. Indexes don't seem to be helping... it simply appears that there is too much processing going on to separate all the records and then determine edit access. One issue is that I can't store the SYS_CONNECT_BY_PATH and index it... determining whether an individual record has edit access consists of comparing if: test_record_sys_path LIKE individual_record_sys_path || '%' Is a materialized view the answer?

    Read the article

  • How to update multiple rows with one single query

    - by xRobot
    I use Postgresql + PHP. Say I have this table: Books ( id, title, year ) and this array of titles in PHP: $titles = array ("bible","kafka","Book of Eli"); now I want update all rows where the title is in the $titles array above. So I need a query like this: UPDATE books SET year = '2001-11-11' WHERE title is in $titles; Is is possible with one single query ? Or do I need to use FOR loop ?

    Read the article

< Previous Page | 14 15 16 17 18 19 20 21 22 23 24 25  | Next Page >